Autres Extraire code SAS egp dans Excel

[suistrop]

Bonjour,

J ai demandé à chatgpt et il me donne le code suivant mais je ne sais pas comment le faire fonctionner ..

# ================== CONFIG ==================
$Root = "C:\chemin\vers\vos\egp" # Dossier racine (parcours récursif)
$Out = "C:\chemin\sortie\egp_code_inventory.csv" # Fichier CSV de sortie
$Delimiter = ';' # ';' conseillé pour Excel FR (',' sinon)
# ============================================

# Assembly .NET zip (utile si Expand-Archive indisponible)
try { Add-Type -AssemblyName System.IO.Compression.FileSystem } catch {}

# -------- Helpers ----------
function New-TempDir {
$d = Join-Path $env:TEMP ([System.IO.Path]::GetRandomFileName())
New-Item -ItemType Directory -Force -Path $d | Out-Null
return (Get-Item $d)
}

function Extract-Egp {
param([string]$EgpPath, [string]$Dest)
try {
Expand-Archive -LiteralPath $EgpPath -DestinationPath $Dest -Force
} catch {
[System.IO.Compression.ZipFile]::ExtractToDirectory($EgpPath, $Dest)
}
}

function Normalize([string]$s) {
if ($null -eq $s) { return "" }
# Normalise les fins de ligne en LF (\n) pour un split cohérent
$s = $s -replace "`r`n","`n"
$s = $s -replace "`r","`n"
return $s
}

function Get-PgmName {
param($XmlNode, [string]$FallbackPath)
$pgm = ""
if ($XmlNode -and $XmlNode.ParentNode -and $XmlNode.ParentNode.Attributes) {
$a = $XmlNode.ParentNode.Attributes.GetNamedItem("Name")
if ($a) { $pgm = $a.Value }
}
if (-not $pgm -and $XmlNode -and $XmlNode.Attributes) {
$a = $XmlNode.Attributes.GetNamedItem("Name")
if ($a) { $pgm = $a.Value }
}
if (-not $pgm) { $pgm = [System.IO.Path]::GetFileNameWithoutExtension($FallbackPath) }
return $pgm
}
# ---------------------------

$rows = New-Object System.Collections.Generic.List[object]
$found = 0
$egpCnt = 0

Get-ChildItem -Path $Root -Recurse -Filter *.egp | ForEach-Object {
$egp = $_.FullName
$egpName = [System.IO.Path]::GetFileName($egp)
$egpCnt++

try {
$tmp = New-TempDir
Extract-Egp -EgpPath $egp -Dest $tmp.FullName

Get-ChildItem -Path $tmp.FullName -Recurse | ForEach-Object {
$p = $_.FullName

if ($_.Extension -ieq ".xml") {
try {
$xml = [xml](Get-Content -LiteralPath $p -Raw -ErrorAction Stop)
# Balises probables contenant du SAS (namespace-agnostic)
$nodes = $xml.SelectNodes("//*[local-name()='Code' or local-name()='Program' or local-name()='SourceCode' or local-name()='SasCode' or local-name()='Text']")
if ($nodes) {
foreach ($n in $nodes) {
# 1) CDATA via InnerText ; 2) fallback InnerXml -> strip tags
$code = $n.InnerText
if ([string]::IsNullOrWhiteSpace($code)) {
$raw = $n.InnerXml
if (-not [string]::IsNullOrWhiteSpace($raw)) {
$code = [System.Text.RegularExpressions.Regex]::Replace($raw, '<.+?>', '')
}
}
$code = Normalize $code
if ($null -eq $code) { continue }

$pgmName = Get-PgmName -XmlNode $n -FallbackPath $p

# ------ OPTION 2: une ligne CSV par ligne SAS ------
$lines = $code -split "`n"
foreach ($line in $lines) {
# On garde l'indentation; on retire seulement les fins d'espaces
$codeLine = if ($null -ne $line) { $line.TrimEnd() } else { "" }
$rows.Add([pscustomobject]@{
EGP = $egpName
PGM = $pgmName
Code = $codeLine
})
$found++
}
}
}
} catch {
Write-Verbose "XML non lisible: $p — $_"
}
}
elseif ($_.Extension -ieq ".sas") {
try {
$code = Get-Content -LiteralPath $p -Raw -ErrorAction Stop
$code = Normalize $code

$pgmName = [System.IO.Path]::GetFileNameWithoutExtension($p)

$lines = $code -split "`n"
foreach ($line in $lines) {
$codeLine = if ($null -ne $line) { $line.TrimEnd() } else { "" }
$rows.Add([pscustomobject]@{
EGP = $egpName
PGM = $pgmName
Code = $codeLine
})
$found++
}
} catch {
Write-Verbose "SAS non lisible: $p — $_"
}
}
}
} catch {
Write-Warning "Erreur sur $egp : $_"
} finally {
if ($tmp -and (Test-Path $tmp.FullName)) { Remove-Item $tmp.FullName -Recurse -Force }
}
}

if ($rows.Count -eq 0) {
Write-Warning "Aucune ligne de code extraite."
}

# Export CSV en 3 colonnes (EGP | PGM | Code), une ligne = une ligne SAS
$rows | Select EGP, PGM, Code | Export-Csv -NoTypeInformation -Encoding UTF8 -Delimiter $Delimiter -Path $Out
Write-Host "EGP analysés : $egpCnt | Lignes exportées : $found"
Write-Host "Sortie : $Out (colonnes EGP$Delimiter PGM$Delimiter Code — 1 ligne SAS par ligne CSV)"

 

[nullosse]

Salut,
ton code c'est du Powershell. il faut que tu demandes à ChatGpt le code en VBA et si ce n'est pas possible, mettre le code Powershell dans un script et l'exécuter.
Nullosse

 

[suistrop]

Merci. Je vais regarder powershell.
En attendant je test aussi cette macro

 

[suistrop]

/* ==== Options utiles perf ==== */
options fullstimer msglevel=i threads compress=no;

/* ==== Macro de stress test (sans sashelp.cars) ==== */
%macro sas_stress(seconds=60, scale=200);
%local t0 _maxid;

/* 1) Jeu de base : uniquement SASHELP.PRDSALE */
proc sql noprint;
create table work._seed_base as
select prodtype, product, country, region, division, year, quarter, month,
actual as measure format=best12.,
predict,
(actual - predict) as resid,
(actual / (predict+1e-6)) as ratio
from sashelp.prdsale;
quit;

/* Amplifier le volume */
data work._big1;
set work._seed_base;
call streaminit(12345);
do _rep_=1 to &scale.;
noise = rand('normal', 0, 5);
jitter = rand('uniform');
measure2 = measure + noise;
output;
end;
drop _rep_;
run;

/* 2) Dimension "lookup" synthétique dérivée de PRDSALE */
proc sql noprint;
create table work._lk_dim as
select product, country, region, division,
count(*) as dim_n,
avg(predict) as dim_avg_pred,
std(predict) as dim_sd_pred,
sum(actual) as dim_sum_act,
monotonic() as dim_id
from sashelp.prdsale
group by product, country, region, division;

/* Récupère une fois pour toutes la borne sup. des IDs */
select max(dim_id) into :_maxid trimmed from work._lk_dim;
quit;

%let t0=%sysfunc(datetime());
%do %while (%sysevalf(%sysfunc(datetime()) - &t0. < &seconds.));

/* 3) Hash join basé sur un ID pseudo-aléatoire 1..&_maxid */
data work._big_joined;
length dim_id 8;

/* Précharger les ATTRIB (types/longueurs) pour éviter tout conflit de type */
if 0 then set work._lk_dim;

if _n_=1 then do;
declare hash H(dataset:"work._lk_dim");
H.defineKey('dim_id');
H.defineData(all:'Y');
H.defineDone();
end;

/* Lire la grosse table AVANT toute référence aux variables communes */
set work._big1;

/* Clé pseudo-aléatoire et lookup */
call streaminit(2718);
dim_id = 1 + floor(rand('uniform') * &_maxid);
rc = H.find();

drop rc;
run;

/* 4) Tri multi-thread (CPU + IO) */
proc sort data=work._big_joined out=work._big_sorted threads;
by region product year month country division;
run;

/* 5) Agrégations lourdes */
proc summary data=work._big_sorted nway;
class region product year country;
var measure measure2 resid ratio dim_n dim_avg_pred dim_sd_pred dim_sum_act;
output out=work._agg1(drop=_type_ _freq_)
sum= sum_measure sum_measure2 sum_resid sum_ratio sum_dim_n sum_dim_avg sum_dim_sd sum_dim_sum
mean= mean_measure mean_measure2 mean_resid mean_ratio mean_dim_n mean_dim_avg mean_dim_sd mean_dim_sum
std= sd_measure sd_measure2 sd_resid sd_ratio sd_dim_n sd_dim_avg sd_dim_sd sd_dim_sum;
run;

/* 6) GROUP BY SQL supplémentaire */
proc sql noprint;
create table work._agg2 as
select year, region, country,
count(*) as n,
sum(sum_measure) as tot_m,
avg(mean_measure2) as avg_m2,
max(sd_resid) as max_sd_resid
from work._agg1
group by year, region, country;
quit;

/* 7) Re-tris + merge pour multiplier les passes disque */
proc sort data=work._agg2 out=work._agg2s threads; by region year country; run;
proc sort data=work._big_sorted out=work._big_sorted2 threads; by region year country; run;

data work._merged;
merge work._big_sorted2(in=a)
work._agg2s(in=b keep=region year country tot_m avg_m2 max_sd_resid);
by region year country;
if a;
score = (measure2 - avg_m2) / (max_sd_resid + 1e-6);
run;

/* 8) TOP-N */
proc sort data=work._merged out=work._top threads;
by descending score;
run;
data work._top100k; set work._top(obs=100000); run;

/* 9) Nettoyage partiel pour contenir WORK */
proc datasets library=work nolist;
delete _agg1 _agg2 _agg2s _big_sorted _big_sorted2 _merged _top;
quit;

%end;

/* Artefact final pour vérification */
proc sql noprint;
select count(*) into :_final_n trimmed from work._top100k;
quit;
%put NOTE: SAS_STRESS terminé. Lignes dans WORK._TOP100K = &_final_n.;

%mend sas_stress;

/* ==== Exécution type ==== */
%sas_stress(seconds=60, scale=200);