topic Re: Calculation from every value in two columns in SAS Programming

Calculation from every value in two columns

adilar39 — Tue, 27 Aug 2024 07:10:09 GMT

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

Re: Calculation from every value in two columns

PeterClemmensen — Tue, 27 Aug 2024 07:27:01 GMT

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

Re: Calculation from every value in two columns

ballardw — Tue, 27 Aug 2024 08:28:49 GMT

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;

Re: Calculation from every value in two columns

ChrisNZ — Tue, 27 Aug 2024 09:40:45 GMT

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

Re: Calculation from every value in two columns

adilar39 — Wed, 28 Aug 2024 03:32:48 GMT

Thank you, it works really well.

Re: Calculation from every value in two columns

adilar39 — Wed, 28 Aug 2024 03:34:54 GMT

This also works well, thank you.

Re: Calculation from every value in two columns

adilar39 — Wed, 28 Aug 2024 03:35:37 GMT

And this one also works well, thank you.