First Post. Going on 3 weeks of SAS experience.
I have run
proc sql;
create table Temp_Freqs as
select distinct VAR1, VAR2, Count(*) as nCount
from FileName
group by VAR1, VAR2;
quit;
It gives a table such as
VAR1 VAR2 nCount
item1 A 1
item1 B 2
item1 C 2
item2 A 3
item2 B 5
item2 C 9
item3 A 3
item3 B 2
item3 C 5
I am trying to create a table with the distinct items as the rows, with num_A, num_B, num_C
as columns with the respective values
such as
VAR1 num_A num_B num_C
item1 1 2 2
item2 3 5 9
item3 3 2 5
which i could then join using proc sql to join on File1.Item1 = File2.Item1 etc.
I have done this with a data step
data num_A num_B num_C;
set FileName;
if VAR2 = 'A' then
output num_A;
drop VAR2;
else if VAR2 = 'B' then
output num_B;
drop VAR2;
else if VAR2 = 'C' then
output num_C;
drop VAR2;
run;
then using proc sql join to join each of these individually to the file to which I would like them joined.
I am convinced there must be a better way, but I have been unsuccessful in finding it.
any help is appreciated.
Check into PROC TRANSPOSE.
DATA mcook_has;
INFILE DATALINES DSD;
INPUT VAR1 $ VAR2 $ nCount;
DATALINES;
item1,A,1
item1,B,2
item1,C,2
item2,A,3
item2,B,5
item2,C,9
item3,A,3
item3,B,2
item3,C,5
;
run;
proc transpose data=mcook_has out=mcook_transposed prefix=num_;
by var1;
id var2;
run;
Check into PROC TRANSPOSE.
DATA mcook_has;
INFILE DATALINES DSD;
INPUT VAR1 $ VAR2 $ nCount;
DATALINES;
item1,A,1
item1,B,2
item1,C,2
item2,A,3
item2,B,5
item2,C,9
item3,A,3
item3,B,2
item3,C,5
;
run;
proc transpose data=mcook_has out=mcook_transposed prefix=num_;
by var1;
id var2;
run;
Not too sure I understand your goal, but 3 comments about the code:
1. No need for the distinct keyword, group by ensures no repetition
2. Use the drop VAR2; statement once only, conventionally near the top of the data step. It is only used at compile time, not at run time.
3. When you want the IF test to run several statements as you seem to, indentations have no effect. You need a do block.
Use indentation consistenly to identify functional blocks, let those blocks start and end on the same column, and keep everything that is in one level on the same column:
proc sql;
create table Temp_Freqs as
select
VAR1,
VAR2,
Count(*) as nCount
from FileName
group by VAR1, VAR2
;
quit;
Your "stairway" paints a misleading picture of the code structure.
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.