DATA Step, Macro, Functions and more

Hospital visit problem

Reply
N/A
Posts: 0

Hospital visit problem

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
Super Contributor
Super Contributor
Posts: 3,174

Re: Hospital visit problem

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
PROC Star
Posts: 1,561

Re: Hospital visit problem

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)(Smiley Happy;

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.
N/A
Posts: 0

Re: Hospital visit problem

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: Hospital visit problem

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.
PROC Star
Posts: 1,561

Re: Hospital visit problem

Agreed, please give it a try and grow your knowledge.
Ask a Question
Discussion stats
  • 5 replies
  • 179 views
  • 0 likes
  • 3 in conversation