Hello,
I have this table :
data have; input x1 x2 x3 x4 x5; cards; 1 . . . . 1 . . . . 4 . . . . 5 . . . . 1 . . . . . 2 . . . . 1 . . . . 2 . . . . 3 . . . . 4 . . . . . 4 . . . . 4 . . . . 1 . . . . 4 . . . . 4 . . . . . 2 . . . . 3 . . . . 4 . . . . 1 . . . . 3 . . . . . 2 . . . . 4 . . . . 3 . . . . 2 . . . . 1 ; run;
I want to transform the table like that:
columns | x1 | x2 | x3 | x4 | x5 |
x1 | 1 | 2 | 4 | 2 | 2 |
x2 | 1 | 1 | 4 | 3 | 4 |
x3 | 4 | 2 | 1 | 4 | 3 |
x4 | 5 | 3 | 4 | 1 | 2 |
x5 | 1 | 4 | 4 | 3 | 1 |
data have;
input a b c d e;
cards;
1 . . . .
1 . . . .
4 . . . .
5 . . . .
1 . . . .
. 2 . . .
. 1 . . .
. 2 . . .
. 3 . . .
. 4 . . .
. . 4 . .
. . 4 . .
. . 1 . .
. . 4 . .
. . 4 . .
. . . 2 .
. . . 3 .
. . . 4 .
. . . 1 .
. . . 3 .
. . . . 2
. . . . 4
. . . . 3
. . . . 2
. . . . 1
;
%let list = a b c d e;
%macro datasets(local_list);
%local index J;
%do index = 1 %to %sysfunc(countw(&local_list));
%let J = %scan(&local_list,&index);
have (keep=&J where=(&J ne .))
%end;
%mend;
data want;
length columns $8;
merge
%datasets(&list)
;
columns = scan("&list",_n_);
run;
proc print data=want noobs;
run;
Result:
columns a b c d e a 1 2 4 2 2 b 1 1 4 3 4 c 4 2 1 4 3 d 5 3 4 1 2 e 1 4 4 3 1
Is your actual problem this simple? Or is this a representation of some more advanced problem?
If it is this simple:
data want;
columns = cats('x', _N_);
merge have (keep=x1 where=(x1))
have (keep=x2 where=(x2))
have (keep=x3 where=(x3))
have (keep=x4 where=(x4))
have (keep=x5 where=(x5));
run;
Result:
columns x1 x2 x3 x4 x5 x1 1 2 4 2 2 x2 1 1 4 3 4 x3 4 2 1 4 3 x4 5 3 4 1 2 x5 1 4 4 3 1
Ok. If you want, we can take a look at 'that program you made' and make it produce readable results.
Otherwise, see if my program above helps you.
%macro datasets;
%do i = 1 %to 5;
have (keep=x&i. where=(x&i. ne .))
%end;
%mend;
data want;
length columns $8;
merge
%datasets
;
columns = cats("x",_n_);
run;
I try this but didn't work
%macro datasets; %let list=CTT_ID SOUU_ID RET_ID REGT_LIBE TIET_IDCCT_ID SOO_ID RE_ID RG_LIBE TE_ID CT_ID OU_ID EG_ID R_LIB TE_ID CT_ID SU_ID ERG_ID RRG_LIBE TTE_ID %do index = 1 %to %sysfunc(countw(&list,%str( ))); %let J =%scan(&list,&index,%str( )); have (keep=J where=(J ne .)) %end; %mend;
%macro datasets;
%local list index j; /* good behavior of macro to prevent side effects */
%let list=
CTT_ID
SOUU_ID
RET_ID
REGT_LIBE
TIET_IDCCT_ID
SOO_ID
RE_ID
RG_LIBE
TE_ID
CT_ID
OU_ID
EG_ID
R_LIB
TE_ID
CT_ID
SU_ID
ERG_ID
RRG_LIBE
TTE_ID
; /* semicolon needed to end the %let */
%do index = 1 %to %sysfunc(countw(&list)); /* blank is the default delimiter of countw */
%let J = %scan(&list,&index); /* same for %scan */
have (keep=&J where=(&J ne .)) /* correctly address macro variables */
%end;
%mend;
@Kurt_Bremser I dont know how to apply the code I have errors
data have; input a b c d e; cards; 1 . . . . 1 . . . . 4 . . . . 5 . . . . 1 . . . . . 2 . . . . 1 . . . . 2 . . . . 3 . . . . 4 . . . . . 4 . . . . 4 . . . . 1 . . . . 4 . . . . 4 . . . . . 2 . . . . 3 . . . . 4 . . . . 1 . . . . 3 . . . . . 2 . . . . 4 . . . . 3 . . . . 2 . . . . 1 ; run; %let list= a b c d e; %macro datasets; %local list index J; %do index = 1 %to %sysfunc(countw(&list)); %let J = %scan(&list,&index); have (keep=&J where=(&J ne .)) %end; %mend; %datasets; data want; length columns $8; merge %datasets ; /*I know it's not like that*/ columns = list; run;
You need to move the %LET for list into the macro, or take it out of the %LOCAL:
%macro datasets;
%local list index J;
%let list = a b c d e;
%do index = 1 %to %sysfunc(countw(&list));
%let J = %scan(&list,&index);
have (keep=&J where=(&J ne .))
%end;
%mend;
%datasets;
data want;
length columns $8;
merge
%datasets
;
/*I know it's not like that*/
columns = scan("&list",_n_);
run;
the code work but still have this error
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 25 26 GOPTIONS ACCESSIBLE; 27 %%macro datasets; 28 %local list index J; 29 %let list = a b c d e; 30 %do index = 1 %to %sysfunc(countw(&list)); 31 %let J = %scan(&list,&index); 32 have (keep=&J where=(&J ne .)) 33 %end; 34 %mend; 35 %datasets; 27 %%macro datasets; _ 180 ERROR 180-322: Statement is not valid or it is used out of proper order.
data have;
input a b c d e;
cards;
1 . . . .
1 . . . .
4 . . . .
5 . . . .
1 . . . .
. 2 . . .
. 1 . . .
. 2 . . .
. 3 . . .
. 4 . . .
. . 4 . .
. . 4 . .
. . 1 . .
. . 4 . .
. . 4 . .
. . . 2 .
. . . 3 .
. . . 4 .
. . . 1 .
. . . 3 .
. . . . 2
. . . . 4
. . . . 3
. . . . 2
. . . . 1
;
%let list = a b c d e;
%macro datasets(local_list);
%local index J;
%do index = 1 %to %sysfunc(countw(&local_list));
%let J = %scan(&local_list,&index);
have (keep=&J where=(&J ne .))
%end;
%mend;
data want;
length columns $8;
merge
%datasets(&list)
;
columns = scan("&list",_n_);
run;
proc print data=want noobs;
run;
Result:
columns a b c d e a 1 2 4 2 2 b 1 1 4 3 4 c 4 2 1 4 3 d 5 3 4 1 2 e 1 4 4 3 1
data have; input a b c d e; cards; 1 . . . . 1 . . . . 4 . . . . 5 . . . . 1 . . . . . 2 . . . . 1 . . . . 2 . . . . 3 . . . . 4 . . . . . 4 . . . . 4 . . . . 1 . . . . 4 . . . . 4 . . . . . 2 . . . . 3 . . . . 4 . . . . 1 . . . . 3 . . . . . 2 . . . . 4 . . . . 3 . . . . 2 . . . . 1 ; run; %let dsid=%sysfunc(open(have)); %let nvar=%sysfunc(attrn(&dsid,nvars)); %let dsid=%sysfunc(close(&dsid)); data temp(index=(n)); set have; n=mod(_n_,&nvar); if n=0 then n=&nvar; run; data want; update temp(obs=0) temp; by n; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.