topic Re: isolating subjects from a database in SAS Programming

isolating subjects from a database

newsasuser — Tue, 01 May 2018 19:10:40 GMT

I am using a databank to isolate subjects with a diagnosis (dcode) from a specific list. In the dataset, each subject (using the identifier inc_key) may have multiple rows if they have more than one diagnosis. In my code I first created a dummy variable (tbi) for each observation that had a diagnosis of interest. Then I tried to extract only subjects with a "tbi" diagnosis, and not use any subjects that either had no "tbi" or that had tbi+ something else. It didn't work though, and Im not sure why. Help would be much appreciated!

macro for the diagnoses Im interested in:

data _null_;
length list $30000 ;
do x=800.0 to 801.9 by 0.01
,803.0 to 804.9 by 0.01
,850.0 to 854.1 by 0.01
,950.1 to 950.3 by 0.01
,959.01
,995.55
;
list=catx(' ',list,put(x,6.2));
end;
call symput('list',list);
run;

creating dummy variable for tbi:

data mt2.tbi1;set ntdb15.rds_dcode;
by inc_key;
tbi=0;
if dcode in (&list) then tbi=1;
run;

output only subjects that have tbi diagnosis and no others:
data mt2.tbiplus ;set mt2.tbi1;

by inc_key;

if first.inc_key=last.inc_key;

if tbi=0 then delete;run;

Re: isolating subjects from a database

Astounding — Tue, 01 May 2018 19:38:20 GMT

First, let me suggest that you switch from CALL SYMPUT to CALL SYMPUTX. That will remove thousands of trailing blanks that are part of &LIST.

Second, you have to picture the BY variables, and which observations are being selected by the final DATA step. The IF statement deletes some, then the DELETE statement deletes more. If a subject has 5 observations, the IF statement deletes the first and last, and only the middle three get inspected by the DELETE statement. That's probably not the correct logic. Here's a way to loop through twice in the same DATA step:

data want;

wanted = 'Y';

do until (last.inc_key);

set ntdb15.rds_dcode;
by inc_key;

if dcode not in (&list) then wanted = 'N';

end;

do until (last.inc_key);

set ntdb15.rds_dcode;
by inc_key;

if wanted = 'Y' then output;

end;

drop wanted;

run;

The top DO loop inspects all the observations for a single INC_KEY, and the bottom DO loop runs through the exact same observations (outputting them if appropriate).

Re: isolating subjects from a database

PaigeMiller — Tue, 01 May 2018 19:42:19 GMT

@newsasuser wrote:

It didn't work though, and Im not sure why.

Could you explain what is happening? Can you show us what you get that is wrong?

Are there errors in the SASLOG? Please show us the SASLOG.

Re: isolating subjects from a database

ballardw — Tue, 01 May 2018 19:45:56 GMT

"Didn't work" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of a dataset, the actual results and the expected results. Data should be in the form of a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Is your DCODE variable numeric or character?

Also you might provide a small example of the critical variables in ntdb15.rds_dcode and then what you expect for the result in mt2.tbiplus.

I suspect that this might be causing one problem:

if first.inc_key=last.inc_key;

which only keeps any records with an inc_key with only one record for further processing.

So if that one record does not have the tbi=1 you delete it with the following line.

Re: isolating subjects from a database

newsasuser — Tue, 01 May 2018 20:17:25 GMT

There were no errors in the log. When I checked a few of the resulting subjects against the original file where I got all the diagnoses, I saw that some of those subjects actually had additional diagnoses that I wasnt interested in. That's what I mean by not working