Dear community, Could you please help me to solve this macro issue. I have a large account numbers dataset and when I tried to get in a macro to optimize my query it is throwing off error (length exceeds default length). So I tried to create different datasets based on ending value of the account number (which is alpha numberial value). But, I have issues doing it. I can do it for an individual dataset. The below code is sample dataset. data new;
input number$;
lastnum=substr(number,length(number));
cards;
1000
2000
3000
1111
1221
3331
22
42
62
222
6933
253
653
263
854
984
1044
1115
985
1785
;
run;
PROC SQL ;
create table new1 as
SELECT DISTINCT (LASTNUM)
FROM NEW; QUIT;
proc sort data=new1;
by lastnum;
run;
DATA _NULL_;
SET NEW1 END=EOF;
BY lastNUM;
X+1;
IF FIRST.lastNUM THEN DO;
CALL SyMPUTX('DSN'||COMPRESS(PUT(X,2.)),lastnum);
end;
if eof then do;
call symputx('tot',_n_);
end;
run;
%put &dsn1**&dsn2**&dsn3**&dsn4**&dsn5**&tot;
%macro test;
%do i=1 %to &tot;
PROC SQL NOPRINT;
SELECT "'"||STRIP(NUMBER)||"'" INTO: NUM&i. SEPARATED BY ","
FROM NEW
WHERE LASTNUM="&&dsn&i.";QUIT;
%end;
%mend;
%test;
%put &num1****&num6;
************************* below query works perfectly, but can't apply to all the 6 macro variables;
proc sql;
select "'"||strip(number)||"'" into:number separated by ','
from new
where lastnum="0";
quit; The desired macro strings should look like this: num1---->'1000','2000','3000' (&num1 should have these values) num2---->'1111','1221','3331' num3---->'22','42','62','222' num4---->'6933','253','653','263' num5---->'854','984','1044' num6---->'1115','985','1785'
... View more
