Hello,
I have a csv file such as some rows are like this (always on the second columns) :
6088 | 06000/06100/06200/06300 | BLABLA | BLIBLI | BLOBLO |
And i'd like that when I import it on SAS, I obtain (for this piece of information) :
6088 |
06000 | BLABLA | BLIBLI |
BLOBLO |
6088 | 06100 | BLABLA | BLIBLI | BLOBLO |
6088 | 06200 | BLABLA | BLIBLI | BLOBLO |
6088 | 06300 | BLABLA | BLIBLI | BLOBLO |
Any idea ?
🙂
If by "on import" you mean use of Proc Import you would do that in a separate data step.
If you have a data step to read the file then AFTER the Input statements in you code you could modify it to do something like:
data want; infile <options>; input <variables> ; <anyother code you want to run before splitting the variable > length shortvar $ 5; /*<= this lenght should be set to hold the longest piece */ do i= 1 to countw(longvar,'/'); shortvar = scan(longvar,i,'/'); output; end; drop i; run;
If you already have the data set, or have used import to create a data set then the above code becomes:
data want; set have; length shortvar $ 5; /*<= this lenght should be set to hold the longest piece */ do i= 1 to countw(longvar,'/'); shortvar = scan(longvar,i,'/'); output; end; drop i; run;
where have is the name of the set you created.
Note: the new variable will not be the second column in any of the results.
If you think that column order is critical then 1) it really seldom is and 2) that question has been addressed many times on this forum.
Please post the REAL contents of your CSV file by copy/pasting into a window opened with </>:
Do NOT open the csv file with Excel or similar; use a text editor (Windows Editor or Notepad++).
A few lines (including an eventual header) should be enough.
If by "on import" you mean use of Proc Import you would do that in a separate data step.
If you have a data step to read the file then AFTER the Input statements in you code you could modify it to do something like:
data want; infile <options>; input <variables> ; <anyother code you want to run before splitting the variable > length shortvar $ 5; /*<= this lenght should be set to hold the longest piece */ do i= 1 to countw(longvar,'/'); shortvar = scan(longvar,i,'/'); output; end; drop i; run;
If you already have the data set, or have used import to create a data set then the above code becomes:
data want; set have; length shortvar $ 5; /*<= this lenght should be set to hold the longest piece */ do i= 1 to countw(longvar,'/'); shortvar = scan(longvar,i,'/'); output; end; drop i; run;
where have is the name of the set you created.
Note: the new variable will not be the second column in any of the results.
If you think that column order is critical then 1) it really seldom is and 2) that question has been addressed many times on this forum.
Actually I have one more issue ^^
My new variable (shortvar) doesn't take into account the leading zeros. So in my example, I get :
Shortvar
6000
6100
6200
6300
instead of :
Shortvar
06000
06100
06200
06300
I tried :
data Want;
set Have;
length shortvar $5.;
Put shortvar z5.;
do i= 1 to countw(longvar,'/');
CodePostal = put(scan(longvar,i,'/'),z5.);
output;
But I have the error message : The format $Z was not found or could not be loaded.
I solved it actually, thanks 🙂
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.