topic Re: Import CSV : Make Slash information as a new lilne in SAS Data Management

Import CSV : Make Slash information as a new lilne

Mathis1 — Thu, 14 May 2020 09:34:02 GMT

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 ?

🙂

Re: Import CSV : Make Slash information as a new lilne

Kurt_Bremser — Thu, 14 May 2020 13:05:44 GMT

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.

Re: Import CSV : Make Slash information as a new lilne

ballardw — Thu, 14 May 2020 13:08:55 GMT

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.

Re: Import CSV : Make Slash information as a new lilne

Mathis1 — Thu, 14 May 2020 13:38:40 GMT

It totally did the job, thank you ! 😄

Re: Import CSV : Make Slash information as a new lilne

Mathis1 — Thu, 14 May 2020 14:13:55 GMT

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.

Re: Import CSV : Make Slash information as a new lilne

Mathis1 — Thu, 14 May 2020 14:47:56 GMT

I solved it actually, thanks 🙂