topic Re: Long to wide and show categories that are not in data set in SAS Programming

Long to wide and show categories that are not in data set

Ronein — Tue, 20 Apr 2021 09:05:48 GMT

Hello

I have a data set with long structure (Each customer has multiple rows).

Each customer has reasons for application to call center .

The task is to re-structure the data set in the following way:

1-I want to change the structure to wide.

2-I want to show also reasons that are not in the raw data.

I have a list of reasons that only these reasons are relevant: 1,2,3,4,5,15,20

What is the way to do it please in most effective way?

Data RawTbl;
Input ID Code;
cards;
1 9
1 3
1 2
2 1
3 2
3 4
4 9
5 1
5 3
5 4
5 8
;
Run;

Re: Long to wide and show categories that are not in data set

andreas_lds — Tue, 20 Apr 2021 09:14:42 GMT

Please show the expected result using the data you have posted, making it easier to check that the description has been understood.

Here is one issue, that needs to be explained

I have a list of reasons that only these reasons are relevant: 1,2,3,4,5,15,20

Does this mean, that you want don't want to see any other reasons? Or do you want to see the reasons in the data plus "relevant reasons" with missing value (or zero)?

Re: Long to wide and show categories that are not in data set

Kurt_Bremser — Tue, 20 Apr 2021 09:38:50 GMT

Transpose to dataset:

Data RawTbl;
Input ID Code;
cards;
1 9
1 3
1 2
2 1
3 2
3 4
4 9
5 1
5 3
5 4
5 8
;

data template;
infile datalines dlm=",";
input code @@;
datalines;
1,2,3,4,5,15,20
;

data all / view=all;
set
  template
  rawtbl (where=(code in (1,2,3,4,5,15,20)))
;
run;

proc transpose
  data=all
  out=want (
    drop=_name_
    where=(id ne .)
  )
  prefix=r_
;
by id;
var code;
id code;
run;

Since wide datasets are mostly useless, I think you want a report:

proc format;
value code
  1 = "1"
  2 = "2"
  3 = "3"
  4 = "4"
  5 = "5"
  15 = "15"
  20 = "20"
;
run;

proc report data=rawtbl (where=(code in (1,2,3,4,5,15,20)));
format code code.;
column id code=value,code;
define id / group;
define code / "" across order=data preloadfmt;
define value / "" analysis;
run;

Result:

ID	1	2	3	4	5	15	20
1	.	2	3	.	.	.	.
2	1	.	.	.	.	.	.
3	.	2	.	4	.	.	.
5	1	.	3	4	.	.	.