topic Observation in dataset each row as variable in SAS Programming

Observation in dataset each row as variable

someone_new — Sun, 08 Nov 2020 06:25:29 GMT

I have a dataset named table1 with only one column named UU

Column =UU
L36
D25
J01

Then i have another raw dataset called table2 (with many columns)
Column1 Column2
Xxx Xxxx
U13 Xxxx
J01 Xxxx
H56 Xxxx
L36 Xxxx

I would like to use table2 and table1 to create as following:

Table J01
Column1 column2
J01 Xxxxxx
J01 Xxxxxx

TableL36
Column1 Column2
L36 Xxxxx
L36 Xxxxx

Thanks

Re: Observation in dataset each row as variable

PeterClemmensen — Sun, 08 Nov 2020 07:06:40 GMT

What is the logic here? Why not D25?

Re: Observation in dataset each row as variable

someone_new — Sun, 08 Nov 2020 07:10:45 GMT

I forgot to include it sorry

Re: Observation in dataset each row as variable

PeterClemmensen — Sun, 08 Nov 2020 07:22:03 GMT

Ok. But you're not interested in U13, correct?

Re: Observation in dataset each row as variable

Kurt_Bremser — Sun, 08 Nov 2020 07:31:08 GMT

Your column2 values in the result do not correspond to those in table2.

Why do you get 2 observations for each key in the result tables, when there's only one each in table2?

Please supply the source data in usable form (data steps with datalines), to remove all doubt about variable attributes and contents, and make the expected result exactly as it would be from those sources.

Note that splitting up tables like this is in most cases not needed and only hinders further processing. You can always use the keys for BY-group processing from the complete dataset.

Re: Observation in dataset each row as variable

chaitali9 — Sun, 08 Nov 2020 16:15:48 GMT

proc sql noprint;
select distinct count(UU) into : cnt from table1;
select distinct UU into :var1 - from table1;
quit;

%macro test;
%do i= 1 %to &cnt;
data &&var&i;
set table2;
where col1= "&&var&i";
run;
%end;

%mend;

%test;

Re: Observation in dataset each row as variable

PGStats — Sun, 08 Nov 2020 18:41:52 GMT

You could use CALL EXECUTE to create each table:

data t1;
input UU $;
datalines;
L36
D25
J01
;

data t2;
input C1 $ C2 $;
datalines;
Xxx Xxxx
U13 Xxxx
J01 Xxxx
H56 Xxxx
L36 Xxxx
;

proc sql;
create view t3 as
select C1 from t2
intersect
select UU from t1;
quit;

data _null_;
set t3;
length command $100;
command = cats("data table", C1, "; set t2; where C1='", C1, "'; run;");
call execute(command);
run;

Here is the log:

 71         
 72         proc sql;
 73         create view t3 as
 74         select C1 from t2
 75         intersect
 76         select UU from t1;
 NOTE: SQL view WORK.T3 a été définie.
 77         quit;
 NOTE: PROCEDURE SQL a utilisé (Durée totale du traitement) :
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 78         
 79         
 80         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 91         
 Soumission : 8 nov. 2020 13:36:21
 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 70         
 71         
 72         data _null_;
 73         set t3;
 74         length command $100;
 75         command = cats("data table", C1, "; set t2; where C1='", C1, "'; run;");
 76         call execute(command);
 77         run;
 
 NOTE: There were 5 observations read from the data set WORK.T2.
 NOTE: There were 3 observations read from the data set WORK.T1.
 NOTE: There were 2 observations read from the data set WORK.T3.
 NOTE: DATA statement a utilisé (Durée totale du traitement) :
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 NOTE: CALL EXECUTE generated line.
 1         + data tableJ01; set t2; where C1='J01'; run;
 
 NOTE: There were 1 observations read from the data set WORK.T2.
       WHERE C1='J01';
 NOTE: The data set WORK.TABLEJ01 has 1 observations and 2 variables.
 NOTE: DATA statement a utilisé (Durée totale du traitement) :
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 2         + data tableL36; set t2; where C1='L36'; run;
 
 NOTE: There were 1 observations read from the data set WORK.T2.
       WHERE C1='L36';
 NOTE: The data set WORK.TABLEL36 has 1 observations and 2 variables.
 NOTE: DATA statement a utilisé (Durée totale du traitement) :
       real time           0.00 seconds
       cpu time            0.01 seconds