BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mazouz
Calcite | Level 5

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:

columnsx1x2x3x4x5
x112422
x211434
x342143
x453412
x514431
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
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

View solution in original post

14 REPLIES 14
PeterClemmensen
Tourmaline | Level 20

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 
mazouz
Calcite | Level 5
it is the result of a program that I made but I want to transform the data structure in order to be more readable
PeterClemmensen
Tourmaline | Level 20

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.

Kurt_Bremser
Super User
%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;
mazouz
Calcite | Level 5

there is another way to loop variable list that doesn't depend on x1 to x5
because my variables in reality have other names
mazouz
Calcite | Level 5

@Kurt_Bremser 

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;
Kurt_Bremser
Super User
%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;
mazouz
Calcite | Level 5

@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;
Kurt_Bremser
Super User

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;
mazouz
Calcite | Level 5

@Kurt_Bremser 

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.
mazouz
Calcite | Level 5
I try it but still same error
Kurt_Bremser
Super User
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
Ksharp
Super User
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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 14 replies
  • 1959 views
  • 2 likes
  • 4 in conversation