topic Re: filename save to file issue in SAS Programming

filename save to file issue

acordes — Thu, 19 Jan 2023 16:17:09 GMT

The following code works, but I cannot figure out to solve 2 challenges that arise.

How can I see or save the file that is generated?

@yabwon , replacing "temp" with "list" does not show me in the log the content of the file.

And how do I have to mask the macro variable in the %put line?

It throws out the error:

ERROR: this is not a legal statement.
NOTE: The submitted statements have been canceled.
158 +%put %NRBQUOTE(&varlist);
"QMERESIG", "QDEVUELT", "IDEVUELT", "ISALDACR", "IMPSALDO", "ANTIGSAL", "IMPNOMIN", "IMPINTER", "IMPIMSEG", "IMPIMPCU", 
"IMPGADEV", "IMPTOTIM", "IMPOMORA", "IMPCOPRA_GT", "IMPINTER_GT", "IMPCOSEA_GT", "IMPCOIVA_GT", "IMCOCOCA_GT", "IMPOMOCA_GT", 
"IMPPRITR_GT", "IMPINTTR_GT", "IMPIMSTR_GT", "IMPIVATR_GT", "IMPGASTR_GT", "IMPMORTR_GT", "IMPSALDO_GT", "IMPCOPRA_CO", 
"IMPINTER_CO", "IMPCOSEA_CO", "IMPCOIVA_CO", "IMCOCOCA_CO", "IMPOMOCA_CO", "IMPPRITR_CO", "IMPINTTR_CO", "IMPIMSTR_CO", 
"IMPIVATR_CO", "IMPGASTR_CO", "IMPMORTR_CO", "IMPSALDO_CO", "IMPCOPRA_GD", "IMPINTER_GD", "IMPCOSEA_GD", "IMPCOIVA_GD", 
"IMCOCOCA_GD", "IMPOMOCA_GD", "IMPPRITR_GD", "IMPINTTR_GD", "IMPIMSTR_GD", "IMPIVATR_GD", "IMPGASTR_GD", "IMPMORTR_GD", 
"IMPSALDO_GD", "IMPCOPRA_GJ", "IMPINTER_GJ", "IMPCOSEA_GJ", "IMPCOIVA_GJ", "IMCOCOCA_GJ", "IMPOMOCA_GJ", "IMPSALDO_GJ", 
"IMPCOPRA_GJ_MDC", "IMPINTER_GJ_MDC", "IMPCOSEA_GJ_MDC", "IMPCOIVA_GJ_MDC", "IMCOCOCA_GJ_MDC", "IMPOMOCA_GJ_MDC", 
"IMPSALDO_GJ_MDC", "IMPPRITR_GJ", "IMPINTTR_GJ", "IMPIMSTR_GJ", "IMPIVATR_GJ", "IMPGASTR_GJ", "IMPMORTR_GJ", "IMPPRITR_FA", 
"IMPINTTR_FA", "IMPIMSTR_FA", "IMPIVATR_FA", "IMPGASTR_FA", "IMPPRITR_FA_GT", "IMPINTTR_FA_GT", "IMPIMSTR_FA_GT", "IMPIVATR_FA_GT", 
"IMPGASTR_FA_GT", "IMPMORTR_FA_GT", "IMPPRITR_FA_CO", "IMPINTTR_FA_CO", "IMPIMSTR_FA_CO", "IMPIVATR_FA_CO", "IMPGASTR_FA_CO", 
"IMPMORTR_FA_CO", "IMPPRITR_FA_GD", "IMPINTTR_FA_GD", "IMPIMSTR_FA_GD", "IMPIVATR_FA_GD", "IMPGASTR_FA_GD", "IMPMORTR_FA_GD", 
"IMPPRITR_FA_GJ", "IMPINTTR_FA_GJ", "IMPIMSTR_FA_GJ", "IMPIVATR_FA_GJ", "IMPGASTR_FA_GJ", "IMPMORTR_FA_GJ", "SALDOMES", "NUMEQDEV", 
"IMPOQDEV", "IMPORVMG", "FEC_EXPLOTACION", "FEC_CARGA", "SW_CARGADO"

filename tempsas temp; /* temporary file, gets deleted when freed or reallocated */
data _null_;
set xdata2;
file tempsas;
put 
/'%let varlist="";'
/'proc cas;'
/'    action columninfo result=r /table={name=' namer  ', caslib="ORACASLIB"};   '
/'    empty = 0;'
/'    if (r.columnInfo.nrows <= 0) then do;'
/'      empty = 1;'
/'      rc = symput("emptyTable", (string)empty);'
/'    end;'
/'    else do;'
/'      columns = "";'
/'        do i = 1 to r.columninfo.nrows;'
/'      if ^index(UPCASE(r["columninfo"][i,4]), "CHAR") or 1=2 THEN do;'
/'            symput("col", quote(r[1,i].column));'
/'            columns = columns||", "||symget("col");'
/'      end;'
/'      end;'
/'      symput("varlist", substrn(columns,2,length(columns)-1));'
/'    end;'
/'run;'
/''
/'quit;'
/'%put %NRBQUOTE(&varlist);';
run;

%include tempsas / source2;

Re: filename save to file issue

Tom — Thu, 19 Jan 2023 16:52:43 GMT

Why do you thing the %PUT statement is the issue? There does not appear to be anything in that macro variable that would require macro quoting.

So this should work fine:

%put &varlist;

If you thought the value might require macro quoting then use a different method to display the value.

%put &=varlist;
%put %superq(varlist);

Re: filename save to file issue

acordes — Thu, 19 Jan 2023 17:00:52 GMT

@Tom please help me to understand.

In my code-generating data _null_ step I'll later take out the

%put &varlist;

It's just for control before running the complete code that uses the macro variable as input statement for the variables to use in a cas action set.

That works fine.

And the

%put &varlist;

in a code window gives the desired result in the log.

But why do I get the error message I reported earlier when I run the line?

%include tempsas / source2;

Re: filename save to file issue

yabwon — Thu, 19 Jan 2023 17:21:05 GMT

the list will show you the _location_ of the file, so you can inspect it manually,

or you can do:

data _null_;
 infile tempsas;
 input;
 putlog _infile_;
run;

to print out content of the file (without running it [like %include does]) in the log.

Bart

Re: filename save to file issue

Tom — Thu, 19 Jan 2023 17:24:47 GMT

Look higher in the SAS log for errors.

Did the DATA _NULL_ step run? How many lines did it write to the file?

What is the purpose of the data step? It looks very strange as it is reading in a dataset with the SET statement, but it does not appear to be doing anything with the values of read. So the only impact of the SET statement is that the PUT statement will run once for each observation read. And since the PUT statement does not reference any of the variables read from the data set it will write the exact same lines of code to the file multiple times.

Re: filename save to file issue

acordes — Thu, 19 Jan 2023 18:04:08 GMT

This line provokes the error with the %include statement.

Now I have removed it and it works.

/''

I show only the part of the code that has sent me into trouble.

The complete code lists all files in the oracle database, then reads the numeric variables into a macro variable in order to pass it as an argument to several cas actions like the dataPreprocess.rustats.

As we're talking about several houndreds of files, I had chosen initially a call execute construct but with the cas actions I derailed with the quoting and || stuff.

So @s_lassen recommended to use an %include approach instead.

Related to this project are other posts I created today, i.e. the one seeking to read an oracle table from last obs backwards.

Re: filename save to file issue

s_lassen — Thu, 19 Jan 2023 22:30:19 GMT

How to see and submit the file that is generated? Open the PROGRAM EDITOR window (not the "enhanced" editor), this can be done with "PGM" on the command line, or from one of the menus. Get a command line there and type "include tempsas", that should do it. To submit the code down to the first "quit;" statement, type "subtop <N>", where <N> is the number of the line with the "quit;" statement.

Alternatively, there is a menu entry with a list of external file references, I think you can browse the fileref TEMPSAS from there.

Third alternative: instead of using a "TEMP" fileref for TEMPSAS, use a physical file, e.g.

filename tempsas 'c:\temp.sas';

and open that file in your favorite editor after generating the code.