topic Creating a table of Grouped Data. in SAS Programming

Creating a table of Grouped Data.

mcook — Sat, 25 Apr 2020 00:48:14 GMT

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.

Re: Creating a table of Grouped Data.

Daryl — Sat, 25 Apr 2020 02:08:38 GMT

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;

Re: Creating a table of Grouped Data.

ChrisNZ — Sun, 26 Apr 2020 01:01:54 GMT

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.

Re: Creating a table of Grouped Data.

Kurt_Bremser — Sat, 25 Apr 2020 10:37:30 GMT

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.