Hello everyone, I'd like to ask if it's possible to do these in SAS.
Consider I have a data like this.
data have;
input id column1-column2 ;
cards;
1 4000 3500
2 4500 3300
3 5000 3100
;
Can SAS do calculation for every value in column1 with every value in column2 and make it into a new table?
I want the result to look like this.
id | column1 | column2 | difference |
1 | 4000 | 3500 | 500 |
2 | 4000 | 3300 | 700 |
3 | 4000 | 3100 | 900 |
4 | 4500 | 3500 | 1000 |
5 | 4500 | 3300 | 1200 |
6 | 4500 | 3100 | 1400 |
7 | 5000 | 3500 | 1500 |
8 | 5000 | 3300 | 1700 |
9 | 5000 | 3100 | 1900 |
Try this
data want;
set have(keep = id column1);
do i = 1 to n;
set have(keep = column2) point = i nobs = n;
difference = column1 - column2;
output;
end;
run;
Result:
id column1 column2 difference 1 4000 3500 500 1 4000 3300 700 1 4000 3100 900 2 4500 3500 1000 2 4500 3300 1200 2 4500 3100 1400 3 5000 3500 1500 3 5000 3300 1700 3 5000 3100 1900
Try this
data want;
set have(keep = id column1);
do i = 1 to n;
set have(keep = column2) point = i nobs = n;
difference = column1 - column2;
output;
end;
run;
Result:
id column1 column2 difference 1 4000 3500 500 1 4000 3300 700 1 4000 3100 900 2 4500 3500 1000 2 4500 3300 1200 2 4500 3100 1400 3 5000 3500 1500 3 5000 3300 1700 3 5000 3100 1900
If the Id is not critical
proc sql; create table dif as select a.column1,b.column2, a.column1-b.column2 as diff from (select column1 from have) as a, (select column2 from have) as b ; quit;
If you need the ID
proc sql;
create table DIF as
select monotonic() as ID
, COLUMN1
, COLUMN2
, COLUMN1-COLUMN2 as DIF
from HAVE(keep=COLUMN1)
, HAVE(keep=COLUMN2)
;
quit;
ID | column1 | column2 | DIFF |
---|---|---|---|
1 | 4000 | 3500 | 500 |
2 | 4000 | 3300 | 700 |
3 | 4000 | 3100 | 900 |
4 | 4500 | 3500 | 1000 |
5 | 4500 | 3300 | 1200 |
6 | 4500 | 3100 | 1400 |
7 | 5000 | 3500 | 1500 |
8 | 5000 | 3300 | 1700 |
9 | 5000 | 3100 | 1900 |
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.
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.
Ready to level-up your skills? Choose your own adventure.