topic Re: Macro Problem in SAS Programming

Macro Problem

Aman4SAS — Tue, 11 Mar 2014 13:20:35 GMT

Hi friends,

I am facing problem in using cats in macro i.e

when i m using this [below] its giving me correct result[here value of &x is being vary by do loop]

%let jb= %qsysfunc(cats(&jb,%str(,),&x));

But when i try to use: %let jb= %sysfunc(cats(&jb,%str(|),&x));

In this case result is only last variable.

Can u please help me on this , I have solved by didnt use any function i.e : %let jb= &jb|&x;

But i m looking for solution with function.

Re: Macro Problem

RW9 — Tue, 11 Mar 2014 13:27:42 GMT

This works fine for me:

%let jb=eee|bbb;

%let x=ccc;

%let jb= %sysfunc(cats(&jb,%str(|),&x));

%put &jb.;

=eee|bbb|ccc

Please supply and example with data as perhaps that is causing your issue (i.e. maybe quotes?).

Re: Macro Problem

Aman4SAS — Tue, 11 Mar 2014 13:49:12 GMT

data test;

input x $;

datalines;

;

run;

%let jb=;

data _null_;

set test;

if _n_=1 then; do;

call symput("x",x);

%let jb= %qsysfunc(cats(&jb,%str(,),&x));

end;

run;

%put &jb;

Re: Macro Problem

RW9 — Tue, 11 Mar 2014 14:01:09 GMT

Ah, I see. You can't do the %let statement in datasteps like that. What you need is call symput, something like:

data test;
input x $;
datalines;
a
b
c
d
e
f
g
;
run;

data _null_;
attrib lstx format=$2000.;
set test end=last;
retain lstx;
if _n_=1 then lstx=x;
else lstx=strip(lstx)||","||strip(x);
if last then do;
call symput('jb',lstx);
end;
run;

%put &jb;

Re: Macro Problem

Aman4SAS — Tue, 11 Mar 2014 14:12:46 GMT

My apology for providing you not exact example, actually program and dataset is very large, I would like to try to provide a portion of programe where i m facing issue.

here jobno, is number of unique subject

jlist is list of subject ie X|Y|Z|......

jobmax is largest one..

in bold where i m facing issue.

%macro findjb();

%do i = 1 %to &jobno;

%let x= %scan(&jlist,&i,%str(|));

proc sort data = jobs_&jobmax; by hash state; run;

proc sort data = jobs_&x; by hash state; run;

data comb;

merge jobs_&jobmax(in=var1) jobs_&x(in=var2);

by hash state;

if var1=0 and var2=1;

run;

proc sql ;

select count(*) into :ctno from comb;

quit;

%put &ctno;

%if &z=1 and &ctno=0 %then %do;

%let jb= &x;

%let z=%eval(&z+1);

%put &jb;

%end;

%else %if &z ne 1 and &ctno =0 and &x ne &jobmax %then %do;

%let jb= %sysfunc(cats(&jb,%str(|),&x));

%put &jb;

%end;

%put &jb;

data finaljb;

set testjobs;

if jobboardid in (&jb);

run;

%mend findjb;

%findjb;

Re: Macro Problem

RW9 — Tue, 11 Mar 2014 14:32:48 GMT

Mmm, having some difficulty following what you are trying to do there. May I suggest to add these couple of lines in:

---

%else %if &z ne 1 and &ctno =0 and &x ne &jobmax %then %do;

%put X: &x.;

%put JB: &jb.;

%let jb= %sysfunc(cats(&jb,%str(|),&x));

%put &jb;

%end;

---

And see what comes out - make sure to have moptions mprint and symbolgen on. It maybe something simple, perhaps try putting double quotes around the &x and &jb for instance = %sysfunc(cats("&jb.",%str(|),"&x."));

Re: Macro Problem

Tom — Tue, 11 Mar 2014 15:55:31 GMT

Why would you be using CATS() function with macro variables?

Re: Macro Problem

Tom — Tue, 11 Mar 2014 15:58:29 GMT

If you want to generate | delimited list of values from a dataset into a macro variable then PROC SQL can do that directly.

proc sql noprint ;

select jobid into :joblist separated by '|'

from mydata

;

quit;