topic Re: PROC SQL macro variable in SAS Programming

PROC SQL macro variable

SASAna — Mon, 21 Nov 2016 20:52:52 GMT

Hi SAS USers,

MANUAL_FILES is not being assigned with DSET values , It is coming as blank.

Below macro i am collecting all datasets & combining them by tilda delimiter in DSET & splitting them in UNIX_FILES Dataset & trying to read one by one in MANUAL_FILES macro variable.

%MACRO INIT_PULL;

libname mydata '/unixpath/';

proc sql;
select memName into:DSET separated by '~'
from dictionary.tables
where libname='MYDATA' and memtype='DATA';
Quit;

%PUT &DSET;

DATA UNIX_FILES;
Length MANUAL_FILES $32767;
%LET i=1;
%do %while (&i <= (%length(compress(&DSET,~,k))) +1);
%let MANUAL_FILES = %scan(&DSET,&i,~);
%let i = %eval(&i+1);
%end;
RUN;

%MEND INIT_PULL;

%INIT_PULL;

Re: PROC SQL macro variable

Reeza — Mon, 21 Nov 2016 21:04:19 GMT

I don't understand what your second step is trying to accomplish.

Can you explain in plain language?

Re: PROC SQL macro variable

Shmuel — Mon, 21 Nov 2016 21:04:28 GMT

Pay attention - is this a typo ?

select memName intoSET separated by '~'

...

%PUT &DSET;

(and what is the smily comes for ?)

Re: PROC SQL macro variable

Reeza — Mon, 21 Nov 2016 21:08:27 GMT

@Shmuel That's the forum converting the text. Colon Capital D -> 😄

Re: PROC SQL macro variable

Shmuel — Mon, 21 Nov 2016 21:20:51 GMT

Thank you @Reeza

It means that original text was (I just aded a blank seperator)

select memName into : DSET separated by '~'

Re: PROC SQL macro variable

SASAna — Mon, 21 Nov 2016 21:22:48 GMT

Hi,

thats ":D" came as smiley , but i was trying to move collect all the datasets into a variable called DSET seperated by :

@Reeza : this datasetep i am using it for looping & splitting as i have to pass one by one datasets & moving into a macro variable manual_files for furthur process. Manual_file is not getting resolved. Not sure if it is &DSET ?

DATA UNIX_FILES;
Length MANUAL_FILES $32767;
%LET i=1;
%do %while (&i <= (%length(compress(&DSET,~,k))) +1); (" I created DSET in previous step which has around 30 datasets sepeated by tilda "~")
%let MANUAL_FILES = %scan(&DSET,&i,~);
%let i = %eval(&i+1);
%end;
RUN;

Re: PROC SQL macro variable

Tom — Mon, 21 Nov 2016 21:44:02 GMT

The DATA step that you wrapped around your macro %DO loop is not doing anything and should be removed. If you want the data in a dataset then just skip the macro variable and have PROC SQL create the dataset for you.

If the goal is to use macro logic to pull names one by one out of the list in the macro variable then just use a normal iterative %DO loop. Note that PROC SQL will tell you how many records it processed with the macro variable SQLOBS so you can set the upperbound of your %DO loop.

proc sql noprint;
  select memName
    into  : dset separated by '~'
    from dictionary.tables
    where libname='MYDATA' and memtype='DATA'
  ;
%let ndset=&sqlobs;
quit;

%do i=1 %to &ndset;
  %let MANUAL_FILES = %scan(&DSET,&i,~);
  %* Do something here ;
  %put &=i &=manual_files ;
%end;

Re: PROC SQL macro variable

Tom — Mon, 21 Nov 2016 21:28:21 GMT

The data step is neither looping or splitting. Since the macro logic is not generating any actual SAS code here is your full data step.

DATA UNIX_FILES;
Length MANUAL_FILES $32767;
RUN;

So it will make one observation dataset with one empty string variable.

Re: PROC SQL macro variable

Shmuel — Mon, 21 Nov 2016 21:28:36 GMT

Then, macro is not needed:

libname mydata '/unixpath/';

proc sql;
select memName intoSET separated by '~'
from dictionary.tables
where libname='MYDATA' and memtype='DATA';
Quit;

%PUT &DSET;

DATA UNIX_FILES;
Length MANUAL_FILES $32767;

do i=1 to countw("&DSET" , '~');
file_x = scan("&DSET" , i , '~' );
end;
RUN;

Re: PROC SQL macro variable

Patrick — Mon, 21 Nov 2016 21:40:27 GMT

Tell us what your end goal is, what you need as a result of your code, i.e. is this about combining 30 tables with the same structure into a single table?

Re: PROC SQL macro variable

SASAna — Mon, 21 Nov 2016 23:49:49 GMT

Thanks Tom. It worked very well , you have helped me minimize many un-necessary codes too .

Thanks again