topic Re: Scan multiple variable values to create one new variable in SAS Programming

Scan multiple variable values to create one new variable

jenim514 — Tue, 12 Jan 2016 20:39:34 GMT

Hi! I have 10 Diagnosis variables (DX1-DX10) and we are creating one new variable (CMDX). We are scanning the 10 DX variable values and as soon as SAS reads 1 of 5 specific DX codes (DOD0301 DOD0302 DOD303 etc...) then we want to copy the DX code so it is the value in the new variable (CMDX).

How might we accomplish this?

Thanks!

Re: Scan multiple variable values to create one new variable

Reeza — Tue, 12 Jan 2016 20:43:23 GMT

What if you have multiple matches...which you most likely will?

You can use either a DO loop with an array or WHICHC function or a combination.

data want;

set have;

array find_dx(5) $ _temporary_ ('E234.34', 'E234.24', 'E456.45', 'B345.23', 'I249.25');

array dx(10) dx1-dx10;

do i=1 to 5;

if whichc(find_dx(i), of dx(*)) then do;

...INSERT YOUR SAS code here;

end;

run;

Re: Scan multiple variable values to create one new variable

jenim514 — Tue, 12 Jan 2016 20:46:42 GMT

@Reeza we are only interested copying the very first match to the new variable.

Re: Scan multiple variable values to create one new variable

Reeza — Tue, 12 Jan 2016 20:48:01 GMT

Then look at changing my loop criteria to end if the value is populated, but either way add your code to the DO/END loop and it should work fine.

Re: Scan multiple variable values to create one new variable

jenim514 — Tue, 12 Jan 2016 21:18:43 GMT

@Reeza Thank you for your help! I am still not sure shat to write in the "Insert your SAS code here". I need it to Copy that identified DX value into the new variable field "CMDX". Here is what I have so far...

data palms.FM01_DX;

set palms.fm01_ela;

array find_dx(4) $ _temporary_ ('DOD0301', 'DOD0302', 'DOD0303', 'Z0289');

array dx(10) dx1-dx10;

do i=1 to 4;

if which (find_dx(i), of dx(*)) then do;

...INSERT YOUR SAS code here;

end;

run;

data palms.FM01_DXCPT;

set palms.FM01_dx;

array find_cpt(5) $ _temporary_ ('G9002', 'G9005', 'G9009', 'G90010',

'G90011');

array cpt(10) cpt_1-cpt_10;

do i=1 to 5;

if which (find_cpt(i), of cpt(*)) then do;

...INSERT YOUR SAS code here;

end;

run;

Re: Scan multiple variable values to create one new variable

Reeza — Tue, 12 Jan 2016 21:26:56 GMT

Why not give it a try? I'm happy to help debug and problem solve.

Re: Scan multiple variable values to create one new variable

jenim514 — Tue, 12 Jan 2016 22:11:25 GMT

@Reeza I used this code, and it partially works, but not quite. It is only populating CMDX when it reads the first DX 'DOD0301'. All the observations should have the CMDX variable populated with onle of the four DX's because all observations contain at least one of these diagnoses... Any help debugging?

data palms.FM01_DX;

set palms.fm01_ela;

array find_dx(4) $ _temporary_ ('DOD0301', 'DOD0302', 'DOD0303',

'Z0289');

array dx(10) dx1-dx10;

do i=1 to 4;

if whichc (find_dx(i), of dx(*)) then do;

CMDX = dx(i);

end;

run;

Re: Scan multiple variable values to create one new variable

Reeza — Tue, 12 Jan 2016 22:23:05 GMT

Try the following modification - it only fills it out once the first condition is met. As I mentioned it's likely that you may have multiple conditions. Beyond that, it looks correct. If it doesn't work, you would need to provide sample data and expected output. From your previous answer you don't need two data steps for the processes. You can declare multiple arrays/loops per data step so you should be able to merge your two data steps into one.

EDIT: Small modification to the do loop condition as well.

data palms.FM01_DX;

set palms.fm01_ela;

array find_dx(4) $ _temporary_ ('DOD0301', 'DOD0302', 'DOD0303',

'Z0289');

array dx(10) dx1-dx10;

do i=1 to 4;

if whichc (find_dx(i), of dx(*))>0 then do;

if missing(CMDX) then CMDX = dx(i);

end;

run;

Re: Scan multiple variable values to create one new variable

jenim514 — Tue, 12 Jan 2016 22:39:27 GMT

@Reeza it seems to almost work, but all the values are now populated with "."

I checked variable format, and it is informat Best12. Is this the problem?

Re: Scan multiple variable values to create one new variable

Reeza — Tue, 12 Jan 2016 22:42:34 GMT

DX array should be declared with a $ sign because it holds character values.

array dx(10) $ dx1-dx10;

Re: Scan multiple variable values to create one new variable

jenim514 — Tue, 12 Jan 2016 22:49:21 GMT

@ReezaI think I'm missing one more $ somewhere? I am still getting the "." and CMDX is still BEST12.

data palms.FM01_DX;

set palms.fm01_ela;

array find_dx(4) $ _temporary_ ('DOD0301', 'DOD0302', 'DOD0303',

'Z0289');

array dx(10)$ dx1-dx10;

do i=1 to 4;

if whichc (find_dx(i), of dx(*))>0 then do;

if missing(CMDX) then CMDX = dx(i);

end;

run;

Re: Scan multiple variable values to create one new variable

Reeza — Tue, 12 Jan 2016 23:02:22 GMT

Do you already have a variable CMDX on the dataset before this step? Is it numeric or character?

You can try to explicity declare CMDX as a character but you shouldn't have too.

data palms.FM01_DX;

set palms.fm01_ela;

length CMDX $8.;

format CMDX $8.;

array find_dx(4) $ _temporary_ ('DOD0301', 'DOD0302', 'DOD0303',

'Z0289');

array dx(10)$ dx1-dx10;

do i=1 to 4;

if whichc (find_dx(i), of dx(*))>0 then do;

if missing(CMDX) then CMDX = dx(i);

end;

run;

Re: Scan multiple variable values to create one new variable

jenim514 — Tue, 12 Jan 2016 23:15:22 GMT

@Reeza , I just removed the whichc function and it seemed to work fine! Thank you for your help!

data palms.FM01_DX;

set palms.fm01_ela;

length CMDX $8.;

format CMDX $8.;

array dx(*) DX1-DX10;

do i= 1 to dim(dx);

if dx(i)in: ('DOD0301', 'DOD0302', 'DOD0303','Z0289') then do;

if missing(CMDX) then CMDX = dx(i);

Output;

return;

end;

run;

Re: Scan multiple variable values to create one new variable

SM1 — Wed, 13 Jan 2016 01:01:29 GMT

I re-ran this syntax on the same data, and everything seems to work fine . . . except that records that do not have the specific ICD 10 codes are deleted. So, before running the syntax, I received this output about the dataset:

NOTE: There were 5267 observations read from the data set PALMS.CMFM0103.

NOTE: The data set PALMS.DATE has 5267 observations and 33 variables.

Next I ran this syntax:

data palms.DX;

set palms.date;

length CMDX $8.;

format CMDX $8.;

array dx(*) DX1-DX10;

do i= 1 to dim(dx);

if dx(i)in: ('DOD0301', 'DOD0302', 'DOD0303','Z0289') then do;

if missing(CMDX) then CMDX = dx(i);

Output;

return;

end;

run;

And here's the log info:

NOTE: There were 5267 observations read from the data set PALMS.DATE.

NOTE: The data set PALMS.DX has 5179 observations and 35 variables.

So the data went from 5,267 records down to only 5,179 records.

What changes can I make to the code so that the value of the new variable CMDX = missing for those records that do not include any of the 4 specific ICD 10 codes listed in the syntax?

Any suggestions will be most appreciated!

Re: Scan multiple variable values to create one new variable

Reeza — Wed, 13 Jan 2016 01:26:01 GMT

In: is not the same as =. If your not looking for exact matches then it might be fine but you can still get unexpected results.

Are the comparisons not an exact match?

Re: Scan multiple variable values to create one new variable

SM1 — Wed, 13 Jan 2016 01:48:12 GMT

They are an exact match.

The records being affected are only those records that don't include any of the 4 ICD 10 codes - they include other ICD 10 codes, just not those 4 specific codes.

Even though they don't contain those codes, I need to keep the records.

I was going to set the new variable CMDX = 0 at the start of the syntax where it's length and format are defined, but if I do that, it would compromise the statement "if missing (CMDX) then CMDX = dx(i);"

Re: Scan multiple variable values to create one new variable

Reeza — Wed, 13 Jan 2016 02:06:21 GMT

Set it to anything else then and check for that value as long as it doesn't conflict with dx codes. But its a character not numeric so 0 isn't valid, but you can use CMDX=0;

Then use
If cmdx=0 then ...

Re: Scan multiple variable values to create one new variable

Reeza — Wed, 13 Jan 2016 03:15:33 GMT

@SM1 wrote:

The records being affected are only those records that don't include any of the 4 ICD 10 codes - they include other ICD 10 codes, just not those 4 specific codes.

Even though they don't contain those codes, I need to keep the records.

All the observations should have the CMDX variable populated with onle of the four DX's because all observations contain at least one of these diagnoses.

I don't think there's anything in the code I provided that deletes records. It would be missing, or you can set it to any other variable you want, including the text 'Missing'. Also, this doesn't match what you stated earlier.

I added in some comments, hopefully it helps you to understand the code better.

data palms.FM01_DX;
set palms.fm01_ela;

*Initialize CMDX variable type and value;
length CMDX $8.;
format CMDX $8.;
CMDX='MISSING';

*Set array that holds codes you're looking for;
array find_dx(4) $ _temporary_ ('DOD0301', 'DOD0302', 'DOD0303',
'Z0289');
*Create array that is for diagnosis codes fields;
array dx(10)$ dx1-dx10;

*Loop over values you're searching for;
do i=1 to 4;
*If a diagnosis that matches a value of interest then assign it to the CMDX;
if whichc (find_dx(i), of dx(*))>0 then do;
if CMDX='MiSSING' then CMDX = dx(i);
end;

end;

run;

Re: Scan multiple variable values to create one new variable

SM1 — Wed, 13 Jan 2016 22:34:25 GMT

Thank you for all the suggestions.

I don't lose the observations so long as I remove the statement "output;"

Thanks again for your help!