PROC SQL macro variable

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 94
Accepted Solution

PROC SQL macro variable

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 intoSmiley Very HappySET 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;

 

 


Accepted Solutions
Solution
‎11-21-2016 06:48 PM
Super User
Super User
Posts: 6,851

Re: PROC SQL macro variable

[ Edited ]

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;

 

View solution in original post


All Replies
Super User
Posts: 19,194

Re: PROC SQL macro variable

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

Can you explain in plain language?

Trusted Advisor
Posts: 1,483

Re: PROC SQL macro variable

Pay attention - is this a typo ?

     select memName intoSmiley Very HappySET separated by '~'

     ...

     %PUT &DSET;

 

 

(and what is the smily comes for ?)

Super User
Posts: 19,194

Re: PROC SQL macro variable

[ Edited ]

@Shmuel That's the forum converting the text. Colon Capital D -> Smiley Very Happy

 

Trusted Advisor
Posts: 1,483

Re: PROC SQL macro variable

Thank you @Reeza

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

    select memName into : DSET separated by '~'

Trusted Advisor
Posts: 1,483

Re: PROC SQL macro variable

Then, macro is not needed:

 

libname mydata '/unixpath/';

proc sql;
select memName intoSmiley Very HappySET 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;

Frequent Contributor
Posts: 94

Re: PROC SQL macro variable

Hi,

 

thats "Smiley Very Happy" 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;

Super User
Super User
Posts: 6,851

Re: PROC SQL macro variable

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.

Respected Advisor
Posts: 4,142

Re: PROC SQL macro variable

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?

Solution
‎11-21-2016 06:48 PM
Super User
Super User
Posts: 6,851

Re: PROC SQL macro variable

[ Edited ]

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;

 

Frequent Contributor
Posts: 94

Re: PROC SQL macro variable

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

Thanks again
☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 420 views
  • 0 likes
  • 5 in conversation