How to count how many time some value appeard with defined ID in PROC SQL in SAS Enterprise Guide?

Question

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 ?

Is it a requirement to use PROC SQL? Does not seem like the right tool. — PeterClemmensen, Sep 15 '22 at 17:28
Do you need a data set or report? It can be done in SQL, if you have only 4 codes it's not so bad, but if you have more it's a definite pain. — Reeza, Sep 15 '22 at 17:29
I have definitely more records than 4, I need a data set and it do no have to be done in PORC SQL but it have to be in SAS Enterprise Guide :) — unbik, Sep 15 '22 at 17:31
Probably better to make a dataset with just three variables, ID SERVICE COUNT, instead of the N+1 variables your design calls for. Then a change in the service types does not change the dataset structure. — Tom, Sep 15 '22 at 17:36
Tom, unfortunately, I need to have an output like as i presented in question, do you have some suggestion, please ? — unbik, Sep 15 '22 at 17:50

score 0 · Answer 1 · answered Sep 15 '22 at 17:35

0

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;

answered Sep 15 '22 at 17:35

PeterClemmensen

4,018
3
14
22

PeterClemmensen, I have Error like "ERROR: The ID value "P1" occurs twice in the same BY group.". In quotation marks is the name of one of the services from SERVICE column - do you know how to fix it or maybe you have other solution, please ? :) – unbik Sep 15 '22 at 17:42
Do you have some values of SERVICE that use uppercase letters, P1, and the same value only with lowercase letters, p1? If not then how could there be duplicates? – Tom Sep 15 '22 at 17:53

Tom · Accepted Answer · 2022-09-15T18:09:36.663

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;

Thank you! best answer given Tom :) – unbik Sep 15 '22 at 18:39 — unbik, Sep 15 '22 at 18:39

How to count how many time some value appeard with defined ID in PROC SQL in SAS Enterprise Guide?

2 Answers2