topic Re: Creating new variable as character, not numeric with mixed char/num data in SAS Data Management

Creating new variable as character, not numeric with mixed char/num data

birmster — Fri, 12 Sep 2014 21:55:54 GMT

I have a dataset that is structured like this:

Person Demographic1 Age Diagnosis1 SecondaryDiagnosis SecondaryDiagnosisPosition

Bob Male 45 424.0 250.1 2

Bob Male 45 424.0 V89.1 3

Bob Male 45 424.0 715.0 4

Jane Female 22 492.11 V25 2

Roger Male 10 720.1

etc etc...

I would like to create a dataset like this:

Person Demographic1 Age Diagnosis1 diagnosis2 diagnosis3 diagnosis4.....etc

Bob Male 45 424.0 250.1 V89.1 715.0

Jane Female 22 492.11 V25

Roger Male 10 720.1

etc...

My first thought was to create a new variable for each secondary diagnosis position (so... diagnosis2, diagnosis3, etc) and have it store the associated diagnosis code. I used this code:

data x; set data y;

diagnosis3 = .;

IF(secondarydiagnosis) =3 then diagnosis3= secondarydiagnosis;

run;

I tried this, but it didn't quite work- it errors out because of the v-codes (character data) inherent in ICD-9 coding. It seems like when I created the new variable, it was automatically created as a numeric variable, when it should've been a character variable. I've tried to switch it to a character, but when I do that, I end up deleting my entire dataset.

data x;

diagnosis2_char = put(diagnosis2, 6.)

drop diagnosis2;

rename diagnosis2_char = diagnosis2;

run;

Primary question: How should I be creating my new variables to get around this issue with the V-codes without deleting my dataset?

Secondary question: I'm not sure that I've chosen the most efficient way to get from the dataset structure I have to the dataset structure that I want- so if there are comments on that, it would be appreciated as well- but my primary problem is the character/ numeric situation.

Thank you.

Re: Creating new variable as character, not numeric with mixed char/num data

PGStats — Fri, 12 Sep 2014 22:23:07 GMT

Try TRANSPOSE:

data have;

infile datalines missover;

input Person $ Demographic1 $ Age Diagnosis1 $ SecondaryDiagnosis $ SecondaryDiagnosisPosition;

datalines;

Bob Male 45 424.0 250.1 2

Bob Male 45 424.0 V89.1 3

Bob Male 45 424.0 715.0 4

Jane Female 22 492.11 V25 2

Roger Male 10 720.1

;

proc transpose data=have out=want(drop=_name_) prefix=Diagnosis;

by Person Demographic1 Age Diagnosis1 notsorted;

var SecondaryDiagnosis;

id SecondaryDiagnosisPosition;

run;

Re: Creating new variable as character, not numeric with mixed char/num data

birmster — Fri, 12 Sep 2014 22:33:47 GMT

Thanks for the reply-- I need to research TRANSPOSE (never used it before). I will try this out- thank you!

Re: Creating new variable as character, not numeric with mixed char/num data

Tom — Fri, 12 Sep 2014 22:44:34 GMT

PROC TRANSPOSE is designed to do this.

Your first attempt created a numeric variable because the first reference to it was when you assigned a numeric value to it. (diagnosis3 = .;)

Your second attempt does not have any data since you forgot to include a SET statement. So it will create a dataset with only one observation.

You were on the right track to a brute force solution. One trick to make it easier is to nest the SET statement inside of a DO loop.

data want;

do until (last.person);

set have;

by person;

if secondarydiagnosisposition =2 then diagnosis2= secondarydiagnosis;

if secondarydiagnosisposition =3 then diagnosis3= secondarydiagnosis;

if secondarydiagnosisposition =4 then diagnosis4= secondarydiagnosis;

if secondarydiagnosisposition =5 then diagnosis5= secondarydiagnosis;

if secondarydiagnosisposition =6 then diagnosis6= secondarydiagnosis;

if secondarydiagnosisposition =7 then diagnosis7= secondarydiagnosis;

if secondarydiagnosisposition =8 then diagnosis8= secondarydiagnosis;

if secondarydiagnosisposition =9 then diagnosis9= secondarydiagnosis;

end;

run;