Hospital visit problem

[deleted_user]

Howdy all, I was given a data set that is bigger than the 1 millions rows that Excel 2007 can handle and I have no other option except doing this in SAS. My data set looks like this, where month 1,2 & 3 are the rolling month period which I need to set up like a macro variable.

PAT_ID month 1 month 2 month 3
1 0 1 2
2 1 1 2
3 2 0 1
4 0 0 3
5 3 0 0
6 0 0 2
7 0 2 0
8 0 2 2

and the end result something like below

PAT_ID month 1 month 2 month 3
1 . 3 .
2 4 . .
3 3 . .
4 . . 3
5 3 .
6 . 4
7 2 .
8 . 4 .

TIA

Don

[sbb]

Using a DATA step with an ARRAY and an IF/THEN statement, test for a zero value and reset the variable to a missing value. The ARRAY can reference the variable prefix for your "month n" variables and use DO I=1 to DIM(); END; to reset the value.

The SAS support http://support.sas.com/ website has SAS-hosted documentation and supplemental technical and conference reference material -- you can use its SEARCH facility or use a Google advanced search (add the site:sas.com to limit the search) using keywords.

Scott Barry
SBBWorks, Inc.

DATA Step Processing
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a001281588.htm

SAS Language Reference: Concepts - Array Processing
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a002299816.htm

Using SAS® Arrays to Manipulate Data
Ben Cochran, The Bedford Group, Raleigh, NC
http://support.sas.com/resources/papers/proceedings09/032-2009.pdfPaper 032-2009

[ChrisNZ]

Use Scott's links to understand the following:

[pre]data OUT;
input PAT_ID MONTH1 MONTH2 MONTH3; * can be written input PAT_ID (MONTH1-MONTH3)(:);

if MONTH1 then do;
MONTH1=sum(of MONTH1-MONTH3);
call missing(MONTH2, MONTH3);
end;
else if MONTH2 then do;
MONTH2=sum(of MONTH1-MONTH3);
call missing(MONTH1, MONTH3);
end;
else if MONTH3 then do;
MONTH3=sum(of MONTH1-MONTH3);
call missing(MONTH1, MONTH2);
end;
else call missing(of MONTH1-MONTH3);
cards;
1 0 1 2
2 1 1 2
3 2 0 1
4 0 0 3
5 3 0 0
6 0 0 2
7 0 2 0
8 0 2 2
;
run;
[/pre]

If you had many months, you'd loop thru them using an array, as Scott said, rather than adding tests.

One step at a time though.

[deleted_user]

Thank you Chris and Scott for your help.

Sorry to bring the bad news but there has been a change of plan.

My data now includes an additional column of the hospital the patient visited and I need the following summary.

Last month last 6 months last 1 year Total Patients
ST-JOHN 50 40 30 120
WESTMEAD 60 40 20 120
CONCORD 30 20 10 60
PRINCE 80 60 40 180
OF WALES

Many thanks for your help.

[sbb]

Given the feedback to-date, I would recommend you attempt to make any necessary SAS code changes, re-post your modified code (after your own testing), and seek feedback again if you have difficulties or questions.

Scott Barry
SBBWorks, Inc.

[ChrisNZ]

Agreed, please give it a try and grow your knowledge.