topic Re: Transformation structure of data in SAS Programming

Transformation structure of data

mazouz — Tue, 10 Nov 2020 09:38:46 GMT

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

Re: Transformation structure of data

PeterClemmensen — Tue, 10 Nov 2020 09:53:33 GMT

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

Re: Transformation structure of data

mazouz — Tue, 10 Nov 2020 09:52:56 GMT

it is the result of a program that I made but I want to transform the data structure in order to be more readable

Re: Transformation structure of data

PeterClemmensen — Tue, 10 Nov 2020 09:55:04 GMT

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.

Re: Transformation structure of data

Kurt_Bremser — Tue, 10 Nov 2020 09:55:53 GMT

%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;

Re: Transformation structure of data

mazouz — Tue, 10 Nov 2020 10:00:35 GMT

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

Re: Transformation structure of data

mazouz — Tue, 10 Nov 2020 10:16:04 GMT

@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;

Re: Transformation structure of data

Kurt_Bremser — Tue, 10 Nov 2020 11:06:16 GMT

%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;

Re: Transformation structure of data

mazouz — Tue, 10 Nov 2020 11:50:53 GMT

@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;

Re: Transformation structure of data

Kurt_Bremser — Tue, 10 Nov 2020 11:57:05 GMT

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;

Re: Transformation structure of data

Ksharp — Tue, 10 Nov 2020 12:00:49 GMT

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;

Re: Transformation structure of data

mazouz — Tue, 10 Nov 2020 12:11:16 GMT

@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.

Re: Transformation structure of data

Kurt_Bremser — Tue, 10 Nov 2020 13:11:43 GMT

Use only one percent sign.

Re: Transformation structure of data

mazouz — Tue, 10 Nov 2020 13:28:53 GMT

I try it but still same error

Re: Transformation structure of data

Kurt_Bremser — Tue, 10 Nov 2020 14:12:23 GMT

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