PROC SQL macro variable

[SASAna]

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;

[Reeza]

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

Can you explain in plain language?

[Shmuel]

Pay attention - is this a typo ?

     select memName intoSET separated by '~'

     ...

     %PUT &DSET;

(and what is the smily comes for ?)

[Reeza]

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

[Shmuel]

Thank you @Reeza

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

    select memName into : DSET separated by '~'

[Shmuel]

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;

[SASAna]

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;

[Tom]

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.

[Patrick]

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?

[Tom · Accepted solution]

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;

[SASAna]

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

Thanks again