What is the logic here? Why not D25?
Ok. But you're not interested in U13, correct?
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.
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;
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
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.