topic Re: How to convert multiple records of one observation to a single record (condense it) in SAS Programming

How to convert multiple records of one observation to a single record (condense it)

mcalde33 — Thu, 20 Oct 2022 02:25:49 GMT

I need to make table A into Table b:

Question 1: Convert the multiple record per patient table to one record per patient

Table A

Emp Id	Jan	Feb	March	April	May	June
1001	0	1	0	0	1	.
1001	.	0	0	0	0	0
1001	.	0	.	1	0	0
1001	0	1	0	0	.	0
1001	1	1	0	0	0	1
1002	1	1	1	1	0	0
1002	0	0	0	0	0	1
1003	1	1	.	1	0	0
1003	.	0	0	0	0	0
1003	0	0	.	.	0	.
1004	.	1	0	0	0	0
1004	1	1	0	0	0	0
1004	1	.	0	0	0	0
1004	0	1	1	1	0	0

Table B

Emp Id	Jan	Feb	March	April	May	June
1001	1	1	0	1	1	1
1002	1	1	1	1	0	1
1003	1	1	0	1	0	0
1004	1	1	1	1	0	0

This is my code so far:

OPTION PS= 58 LS= 72 NODATE NONUMBER;


DATA DM2022.Patients;
      SET patientsdata;
      By emplid;


Run;
   
        
PROC PRINT data= patients NOOBS;
      title 'Table B';
RUN;

Re: How to convert multiple records of one observation to a single record (condense it)

Reeza — Thu, 20 Oct 2022 02:44:21 GMT

Take the maximum of each column grouping by ID

proc means data=dm2022.patients noprint NWAY;
class emplID;
var Jan--June;
output out=grouped_data max = ;
run;

I have questions about your code though...

Is your data/set statement backwards in the code you posted? And then no libraries reference in the print although it is assigned it to a library?

The options specified at the top of the code are usually seen in older SAS code that used the listing output not HTML one. Some of those do not apply to ODS HTML if that is the destination (displayed results file format).

@mcalde33 wrote:

I need to make table A into Table b:

Question 1: Convert the multiple record per patient table to one record per patient

Table A

Emp Id

Jan

Feb

March

April

May

June

1001

0

1

0

0

1

.

1001

.

0

0

0

0

0

1001

.

0

.

1

0

0

1001

0

1

0

0

.

0

1001

1

1

0

0

0

1

1002

1

1

1

1

0

0

1002

0

0

0

0

0

1

1003

1

1

.

1

0

0

1003

.

0

0

0

0

0

1003

0

0

.

.

0

.

1004

.

1

0

0

0

0

1004

1

1

0

0

0

0

1004

1

.

0

0

0

0

1004

0

1

1

1

0

0

Table B

Emp Id

Jan

Feb

March

April

May

June

1001

1

1

0

1

1

1

1002

1

1

1

1

0

1

1003

1

1

0

1

0

0

1004

1

1

1

1

0

0

This is my code so far:
OPTION PS= 58 LS= 72 NODATE NONUMBER; 


DATA DM2022.Patients;
      SET patientsdata;
      By emplid;


Run;
   
        
PROC PRINT data= patients NOOBS;
      title 'Table B';
RUN;

Re: How to convert multiple records of one observation to a single record (condense it)

mcalde33 — Thu, 20 Oct 2022 12:28:13 GMT

Yes the SET "patientsdata" is referencing a temp dataset with the data as shown in table a

Re: How to convert multiple records of one observation to a single record (condense it)

PeterClemmensen — Thu, 20 Oct 2022 12:34:21 GMT

@mcalde33 did @Reezas code work for you?

Re: How to convert multiple records of one observation to a single record (condense it)

mcalde33 — Thu, 20 Oct 2022 16:03:50 GMT

No it did not.

Here is my code:

OPTION PS= 58 LS= 72 NODATE NONUMBER;

72 DATA DM2022.Patients;

73 SET patientsdata;

ERROR: File WORK.PATIENTSDATA.DATA does not exist.

74 By emplid;

75 Run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set DM2022.PATIENTS may be incomplete. When this

step was stopped there were 0 observations and 0 variables.

WARNING: Data set DM2022.PATIENTS was not replaced because this step

was stopped.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 535.25k

OS Memory 24996.00k

Timestamp 10/20/2022 03:40:45 PM

Step Count 52 Switch Count 0

Page Faults 0

Page Reclaims 68

Page Swaps 0

Voluntary Context Switches 19

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 8

77 PROC MEANS data=dm2022.patients NOPRINT NWAY;

78 class emplid;

ERROR: Variable EMPLID not found.

79 var Jan Feb March April May June;

80 output out=grouped_data max = ;

81 run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.GROUPED_DATA may be incomplete. When this

step was stopped there were 0 observations and 0 variables.

WARNING: Data set WORK.GROUPED_DATA was not replaced because this step

was stopped.

NOTE: PROCEDURE MEANS used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 500.90k

OS Memory 25256.00k

Timestamp 10/20/2022 03:40:45 PM

Step Count 53 Switch Count 0

Page Faults 0

Page Reclaims 53

Page Swaps 0

Voluntary Context Switches 3

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 8

83 PROC PRINT data= patients NOOBS;

84 title 'Table B';

85 RUN;

NOTE: There were 14 observations read from the data set WORK.PATIENTS.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.02 seconds

user cpu time 0.03 seconds

system cpu time 0.00 seconds

memory 1140.15k

OS Memory 25256.00k

Timestamp 10/20/2022 03:40:45 PM

Step Count 54 Switch Count 1

Page Faults 0

Page Reclaims 100

Page Swaps 0

Voluntary Context Switches 7

Involuntary Context Switches 1

Block Input Operations 0

Block Output Operations 8

88 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

THIS IS THE OUTPUT THAT CAME OUT:

Table B

EmpID Jan Feb March April May June

1001	0	1	0	0	1	.
1001	.	0	0	0	0	0
1001	.	0	.	1	0	0
1001	0	1	0	0	.	0
1001	1	1	0	0	0	1
1002	1	1	1	1	0	0
1002	0	0	0	0	0	1
1003	1	1	.	1	0	0
1003	.	0	0	0	0	0
1003	0	0	.	.	0	.
1004	.	1	0	0	0	0
1004	1	1	0	0	0	0
1004	1	.	0	0	0	0
1004	0	1	1	1	0	0

Re: How to convert multiple records of one observation to a single record (condense it)

Reeza — Thu, 20 Oct 2022 16:12:28 GMT

It's not working because the data doesn't exist though - not an issue with the code.

What is the name of the input data set?

Please show a proc contents on that data, e.g.

proc contents data=patientsData;
run;

The code you posted is not relevant to the question being asked, but if it doesn't work then the next steps will not work.

Re: How to convert multiple records of one observation to a single record (condense it)

mcalde33 — Thu, 20 Oct 2022 16:39:46 GMT

This is what im using to "set" the data.

DATA Patients;
INPUT EmpID 1-4 Jan 6 Feb 8 March 10 April 12 May 14 June 16;

DATALINES;
1001 0 1 0 0 1 .
1001 . 0 0 0 0 0
1001 . 0 . 1 0 0
1001 0 1 0 0 . 0
1001 1 1 0 0 0 1
1002 1 1 1 1 0 0
1002 0 0 0 0 0 1
1003 1 1 . 1 0 0
1003 . 0 0 0 0 0
1003 0 0 . . 0 .
1004 . 1 0 0 0 0
1004 1 1 0 0 0 0
1004 1 . 0 0 0 0
1004 0 1 1 1 0 0
;
RUN;

Re: How to convert multiple records of one observation to a single record (condense it)

Reeza — Thu, 20 Oct 2022 16:56:48 GMT

DATA Patients;
INPUT EmpID 1-4 Jan 6 Feb 8 March 10 April 12 May 14 June 16;

DATALINES;
1001 0 1 0 0 1 .
1001 . 0 0 0 0 0
1001 . 0 . 1 0 0
1001 0 1 0 0 . 0
1001 1 1 0 0 0 1
1002 1 1 1 1 0 0
1002 0 0 0 0 0 1
1003 1 1 . 1 0 0
1003 . 0 0 0 0 0
1003 0 0 . . 0 .
1004 . 1 0 0 0 0
1004 1 1 0 0 0 0
1004 1 . 0 0 0 0
1004 0 1 1 1 0 0
;
RUN;

*summarize each ID to one row per ID taking the maximum value for each month;
proc means data=patients noprint NWAY;
class empID;
var Jan--June;
output out=grouped_data max = ;
run;

*display results from summarization step;
proc print data=grouped_data;
run;

The above works as expected. I'm not sure what the rest of your code was intended to do, therefore not sure if it's needed.

I don't know what you were inten

Re: How to convert multiple records of one observation to a single record (condense it)

mcalde33 — Thu, 20 Oct 2022 17:40:14 GMT

this worked and I got this:
Table B

EmpID _TYPE_ _FREQ_ Jan Feb March April May June
1001 1 5 1 1 0 1 1 1
1002 1 2 1 1 1 1 0 1
1003 1 3 1 1 0 1 0 0
1004 1 4 1 1 1 1 0 0

But i dont want the columns type and freq.
Where do I add the NOFREQ? and NOTYPE?

Re: How to convert multiple records of one observation to a single record (condense it)

Reeza — Thu, 20 Oct 2022 17:44:41 GMT

proc means data=patients noprint NWAY;
class empID;
var Jan--June;
output out=grouped_data (drop = _freq_ _type_) max = ;
run;

Use a dataset drop option to drop the variables in the proc means step.