Desktop productivity for business analysts and programmers

Macro in Data infile statement csv file

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Macro in Data infile statement csv file

[ Edited ]

Hi,

Is my first time with SAS's macro and i want to import several csv file with a data step using INFILE (to can change the formats and informats).
I tried this code but i don't know how to define a macro's variable to make variate the data set's name for each file imported.

I think that the statement "(table_name=)" is wrong but i don't know how to specify this.

I need your help, thank you very much!

 

 

%MACRO import (fichier=) (table_name=);
DATA WORK.&table_name;
INFILE "/data/XXX/Lignes/&fichier"
LRECL=603
ENCODING="LATIN9"
TERMSTR=CRLF
DLM=';' FIRSTOBS=2
MISSOVER
DSD ;
INPUT
'VARIABLE A'n : $27.
vARIABLE B: $34.

FORMAT
'VARIABLE A'n $27.
vARIABLE B $34.

INFORMAT
'VARIABLE A'n $27.
vARIABLE B $34.

RUN;

%MEND;

%import (fichier=file_AB.csv);
%import (table_name=table_sas_AB);

 


Accepted Solutions
Solution
4 weeks ago
Valued Guide
Posts: 559

Re: Macro in Data infile statement csv file

Posted in reply to luciacossaro
%MACRO import (fichier=,table_name=);
DATA WORK.&table_name;
INFILE "/data/XXX/Lignes/&fichier"
LRECL=603
ENCODING="LATIN9"
TERMSTR=CRLF
DLM=';' FIRSTOBS=2
MISSOVER
DSD ;
INPUT
'VARIABLE A'n : $27.
vARIABLE B: $34.

FORMAT
'VARIABLE A'n $27.
vARIABLE B $34.

INFORMAT
'VARIABLE A'n $27.
vARIABLE B $34.

RUN;

%MEND;

%import (fichier=file_AB.csv, table_name=table_sas_AB);

 

For this code to work all the files your trying to import must have same data pattern as you mentioned in INPUT statement.

Thanks,
Suryakiran

View solution in original post


All Replies
Solution
4 weeks ago
Valued Guide
Posts: 559

Re: Macro in Data infile statement csv file

Posted in reply to luciacossaro
%MACRO import (fichier=,table_name=);
DATA WORK.&table_name;
INFILE "/data/XXX/Lignes/&fichier"
LRECL=603
ENCODING="LATIN9"
TERMSTR=CRLF
DLM=';' FIRSTOBS=2
MISSOVER
DSD ;
INPUT
'VARIABLE A'n : $27.
vARIABLE B: $34.

FORMAT
'VARIABLE A'n $27.
vARIABLE B $34.

INFORMAT
'VARIABLE A'n $27.
vARIABLE B $34.

RUN;

%MEND;

%import (fichier=file_AB.csv, table_name=table_sas_AB);

 

For this code to work all the files your trying to import must have same data pattern as you mentioned in INPUT statement.

Thanks,
Suryakiran
Super User
Posts: 6,628

Re: Macro in Data infile statement csv file

Posted in reply to luciacossaro

That's reasonably close.  You need to combine the macro calls into one.  First, when defining the macro:

 

%macro import (fichier=, table_name=);

 

Then when calling the macro:

 

%import (fichier=file_AB.csv, table_name=table_sas_AB)

 

Also note, it looks like  you are missing a few semicolons.  The INPUT, FORMAT, and INFORMAT statements all should end with a semicolon.  In this particular program, however, it wouldn't hurt anything to just get rid of the FORMAT and INFORMAT statements.

Contributor
Posts: 24

Re: Macro in Data infile statement csv file

Posted in reply to Astounding

Thank you very much for your reponse. Yes, the code paste here was a little part from the original code, i forgotten a semicolons when i do copy-paste but in the original there are all semicolons. 

Super User
Posts: 13,300

Re: Macro in Data infile statement csv file

Posted in reply to luciacossaro

If all of these "files" have the same layout and you expect to combine the data together (a very common activity with similar datasets) you would be much better off assigning a single informat, possibly a little longer than the longest expected value and then use the same program to import all of them with the same characteristics.

 

Otherwise when you go to combine the data you will get messages about variables with different lengths in different data sets possibly resulting in truncated data. A likely actually truncated data.

 

And unless there is supervisor standing over me with a whip I would never use a name literal variable name like 'Variable A'n as that just adds to lots of headaches keeping that spelled correctly in code.

Contributor
Posts: 24

Re: Macro in Data infile statement csv file

[ Edited ]

Thank you very much for your reponse. Yes, in the original code i chosse the maximal value for every variable for all files and the names variable A was only to describe the structure of code; but is not the name of the variables which i use.

Now i hqave another problem.

To know which informat choose in every variable, I did before an import with the sas's assistant for one file and I copied the program from the log and I modified thant a little. 
The problem is: in this code there is a parameter 'LRECL' with a determinate value choosed by Sas but if I set the same value (or the default value) for all files the informat and format choosed do not work in the others files, and is very difficult to know which value set for every file because I have a lot of files.
Do you know another way to make that? 
I don't understand why the format and informat works in this situation and do not work in another.
Contributor
Posts: 24

Re: Macro in Data infile statement csv file

Posted in reply to luciacossaro

Thanks you very much! It's work, this is what i needed!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 179 views
  • 0 likes
  • 4 in conversation