I have Table in SAS Enterprise Guide like below: Data type:

• ID - numeric

• SERVICE - character

  ID	SERVICE
  123	P1
  123	P1
  123	G
  444	AB
  56	c2

And I need to know how many time each ID bought each SERVICE, so as a result I need something like below:

ID  | P1| G | AB | c2
----|---|---|----|---
123 | 2 | 1 | 0  | 0
444 | 0 | 0 | 1  | 0
56  | 0 | 0 | 0  | 1

• Because for example ID = 123 bought SERVICE = P1 2 times and 0 times bought G, AB or c2 and so on in terms of other IDs.

How can I do that in PROC SQL in SAS Enterprise Guide ?

CodePudding user response：

If you just want to make that REPORT then you can do it directly from the data use PROC TABULATE.

data have;
  input ID SERVICE $;
cards;
123 P1
123 P1
123 G
444 AB
56 c2
;

proc tabulate data=have ;
  class id service;
  table id,service*N / printmiss misstext='0';
  keylabel n=' ';
run;

If you need to save the counts into a dataset then use PROC FREQ to count.

proc freq data=have ;
   tables id*service / noprint out=want sparse;
run;

You could then make that REPORT using PROC REPORT. ID is the grouping variable and SERVICE is the ACROSS variable.

proc report data=want ;
    column id count,service;
    define id / group;
    define service / across ' ';
run;

If you need an actual dataset (why???) then use PROC TRANSPOSE. But then the values of SERVICE have to be valid SAS names so they can be used to name the variables in the dataset.

 proc transpose data=want out=wide (drop=_name_ _label_);
    by id;
    id service;
    var count;
 run;
 proc print data=wide;
 run;

CodePudding user response：

Try this

data have;
input ID SERVICE $;
datalines;
123 P1 
123 P1 
123 G  
444 AB 
56  c2 
;

proc summary data = have nway;
   class ID Service;
   output out = temp(drop = _type_ rename = _freq_ = n);
run;

proc transpose data = temp out = want(drop = _:);
    by ID ;
    id Service;
    var n;
run;