topic Re: Macro in Data infile statement csv file in SAS Enterprise Guide

Macro in Data infile statement csv file

luciacossaro — Wed, 23 May 2018 19:44:01 GMT

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);

Re: Macro in Data infile statement csv file

SuryaKiran — Wed, 23 May 2018 20:10:57 GMT

%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.

Re: Macro in Data infile statement csv file

Astounding — Wed, 23 May 2018 20:11:32 GMT

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.

Re: Macro in Data infile statement csv file

ballardw — Wed, 23 May 2018 20:37:15 GMT

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.

Re: Macro in Data infile statement csv file

luciacossaro — Fri, 25 May 2018 07:14:06 GMT

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

Re: Macro in Data infile statement csv file

luciacossaro — Fri, 25 May 2018 07:17:05 GMT

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.

Re: Macro in Data infile statement csv file

luciacossaro — Fri, 25 May 2018 07:24:38 GMT

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.