Creating a table of Grouped Data.

[mcook]

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.  

[Daryl · Accepted solution]

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;

[ChrisNZ]

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.

[Kurt_Bremser]

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.