Creating new variables

[sks521]

Hi folks,

I am trying to write a code for the attached data. Variables are from 'New_code' to 'Sixth_code'. I want to create a new variable for e.g. 'Respire'=1 where any of the below variables are in  'Respiratory' or 'Clinical_laboratory' , otherwise, 'Respire'=0;

I have written the following syntax;

data respiratory;
set restrict;
if New_code in ('respiratory', 'clinical_laboratory') or First_code in ('respiratory', 'clinical_laboratory') or Second_code in ('respiratory', 'clinical_laboratory') or
Third_code in ('respiratory', 'clinical_laboratory') or Fourth_code in ('respiratory', 'clinical_laboratory') or Fifth_code in ('respiratory', 'clinical_laboratory') or
Sixth_code in ('respiratory', 'clinical_laboratory') then respire=1;
else respire=0;
run; 

New_code	First_code	Second_code	Third_code	Fourth_code	Fifth_code	Sixth_code
Clinical_laboratory	Respiratory	Ungrouped	Ungrouped	Ungrouped	Ungrouped	Ungrouped
Infections	Clinical_laboratory	Ungrouped	Ungrouped	Ungrouped	Ungrouped	Ungrouped
Injuries	Injuries	Ungrouped	Ungrouped	Ungrouped	Ungrouped	Ungrouped
Respiratory	Congenital	Potential_hazard	Endocrine	Ungrouped	Ungrouped	Ungrouped
Subcutanous	Infections	Ungrouped	Ungrouped	Ungrouped	Ungrouped	Ungrouped
Digestive	Ungrouped	Ungrouped	Ungrouped	Ungrouped	Ungrouped	Ungrouped
Digestive	Congenital	Ungrouped	Congenital	Congenital	Ear_mastoid	Ungrouped

 which is giving 'Respire'=0 for all observations .

Can someone help please?

Ta

S 

[PaigeMiller]

I think the problem with your  code is that the data is mixed case, whereas your IF statements are searching for lower case text. So no matches will happen.

A quicker way to program this is something like:

data want;
    set have;
    array code new_code--sixth_code;
    respire = whichc('Respiratory',of code{*})>0 
        or whichc('Clinical_laboratory',of code{*})>0;
run;

[sks521]

Thanks. I tried it but the new variable 'Respire' still has 0 values for all obs.

[PaigeMiller]

---

@sks521 wrote:

Thanks. I tried it but the new variable 'Respire' still has 0 values for all obs.

---

First, please examine your code and also look at the exact SAS data set being used, to see if the case and spelling of the data values match. If the SAS data set is not created properly, then "Clinical_laboratory" might get truncated to eight characters and won't match.

If that doesn't help, then we'd need to see your exact code that you used, and also you would need to provide a portion of the data following these instructions: How to create a data step version of your data AKA generate sample data for forums

[sks521]

data WORK.RESTRICT;
  infile datalines dsd truncover;

input apc1_apc2_link:$32. pkid:$32. dateofbirth:$8. ethnicgroup:$2. sex:$1. hospitalproviderspellnumber:$32. administrativecategory:$1. admissionmethodhospitalproviders:$2. dischargemethodhospitalproviders:$1. sourceofadmissionhospitalprovide:$2. startdate
hospitalproviderspell:$8. starttimehospitalproviderspell:$8. dischargedatefromhospitalprovide:$8. dischargetimehospitalproviderspe:$8. episodenumber:$1. startdateconsultantepisode:$8. enddateconsultantepisode:$8. consultantcode:$8. mainspecialtycode:$3. di
agnosisprimaryicd:$6. diagnosis1stsecondaryicd:$6. diagnosis2ndsecondaryicd:$6. diagnosis3rdsecondaryicd:$6. diagnosis4thsecondaryicd:$6. diagnosis5thsecondaryicd:$6. diagnosis6thsecondaryicd:$6. hospitalspellduration:$1. Start_date:DATE10. Dis_date:DATE10
. age:32. VISIT:32. age1:32. admission:32. YEAR:32. quarter:32. month:32. New_code:$6. First_code:$6. Second_code:$6. Third_code:$6. Fourth_code:$6. Fifth_code:$6. Sixth_code:$6. age2:32.;
  format Start_date DATE10. Dis_date DATE10.;
datalines;
1.43410300322e+18 235031 199809 J 1 100781305 1 21 1 19 20150409 02:34:00 20150409 10:50:00 1 20150409 20150409 C6057650 300 R55X J459           0 09APR2015 09APR2015 9 1 1 1 4 14 40 Clinical_laboratory Respiratory Ungrouped Ungrouped Ungrouped Ungrouped
Ungrouped 1
1.43410300322e+18 1211513 201106 99 2 100784128 1 21 1 19 20150416 04:00:00 20150416 02:45:00 1 20150416 20150416 C6101983 420 B349 R062           1 16APR2015 16APR2015 2 1 1 1 4 14 40 Infections Clinical_laboratory Ungrouped Ungrouped Ungrouped Ungrouped
Ungrouped 1
1.43410300322e+18 108097 200407 J 2 100781392 1 21 1 19 20150409 07:30:00 20150409 03:35:00 1 20150409 20150409 C4340568 160 S611 W239           0 09APR2015 09APR2015 15 1 1 1 4 14 40 Injuries Injuries Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped 1
1.43410300322e+18 606892 201304 A 1 100787788 1 22 1 19 20150427 01:00:00 20150429 02:30:00 1 20150427 20150429 C3546411 420 J181 Q909 Z931 E835       0 27APR2015 29APR2015 16 1 1 1 4 14 40 Respiratory Congenital Potential_hazard Endocrine Ungrouped
Ungrouped Ungrouped 1
1.43410300322e+18 1084927 200910 S 1 100786427 1 28 1 19 20150423 08:00:00 20150423 04:16:00 1 20150423 20150423 C4673523 160 L030 B956           0 23APR2015 23APR2015 10 1 1 1 4 14 40 Subcutanous Infections Ungrouped Ungrouped Ungrouped Ungrouped
Ungrouped 1
1.43410300322e+18 391210 200811 99 1 100783594 1 28 1 19 20150414 10:30:00 20150415 04:00:00 1 20150414 20150415 C6145391 145 K047             0 14APR2015 15APR2015 1 1 1 1 4 14 40 Digestive Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped 0
1.43410300322e+18 311756 200311 J 1 100786148 1 22 1 19 20150422 11:15:00 20150423 10:25:00 1 20150422 20150423 C3413878 100 K566 Q250 I272 Q251 Q336 H905   1 22APR2015 23APR2015 7 1 1 1 4 14 40 Digestive Congenital Ungrouped Congenital Congenital
Ear_mastoid Ungrouped 1
1.43410300322e+18 326905 199905 A 1 100784971 1 28 1 19 20150419 09:00:00 20150419 05:30:00 1 20150419 20150419 C2611493 160 S618 W260           1 19APR2015 19APR2015 13 1 1 1 4 14 40 Injuries Injuries Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped 1
1.43410300322e+18 1118340 201003 A 2 100785020 1 22 1 19 20150419 06:50:00 20150420 03:44:00 1 20150419 20150420 C4217176 100 N390             0 19APR2015 20APR2015 7 1 1 1 4 14 40 Genitourinary Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped 1
1.43410300322e+18 538555 200404 J 1 100799443 1 28 1 19 20150416 10:30:00 20150416 04:00:00 1 20150416 20150416 C3451324 420 N390 N270 N288 J459 Z936 Z933   1 16APR2015 16APR2015 1 1 1 1 4 14 40 Genitourinary Genitourinary Genitourinary Respiratory
Potential_hazard Potential_hazard Ungrouped 0
;;;;

I have used the following code;

data Respiratory;
set restrict;
array code new_code First_code Second_code Third_code Fourth_code Fifth_code sixth_code;
respire = whichc('Respiratory',of code{*})>0
or whichc('Clinical_laboratory',of code{*})>0;
run;

[PaigeMiller]

Unfortunately, that code didn't work 😞

Its not reading the data properly. Please look at your own SAS data set and see if it is reading the data properly, particularly the variables of interest new_code through sixth_code.

[sks521]

data restrict;
  infile datalines dsd truncover;

input apc1_apc2_link:$32. pkid:$32. dateofbirth:$8. ethnicgroup:$2. sex:$1. hospitalproviderspellnumber:$32. 
administrativecategory:$1. admissionmethodhospitalproviders:$2. dischargemethodhospitalproviders:$1. sourceofadmissionhospitalprovide:$2. startdate
hospitalproviderspell:$8. starttimehospitalproviderspell:$8. dischargedatefromhospitalprovide:$8. 
dischargetimehospitalproviderspe:$8. episodenumber:$1. startdateconsultantepisode:$8. enddateconsultantepisode:$8. consultantcode:$8. mainspecialtycode:$3. di
agnosisprimaryicd:$6. diagnosis1stsecondaryicd:$6. diagnosis2ndsecondaryicd:$6. diagnosis3rdsecondaryicd:$6. 
diagnosis4thsecondaryicd:$6. diagnosis5thsecondaryicd:$6. diagnosis6thsecondaryicd:$6. hospitalspellduration:$1. Start_date:DATE10. Dis_date:DATE10.
age:32. VISIT:32. age1:32. admission:32. YEAR:32. quarter:32. month:32. New_code:$6. First_code:$6. Second_code:$6. 
Third_code:$6. Fourth_code:$6. Fifth_code:$6. Sixth_code:$6. age2:32.;
  format Start_date DATE10. Dis_date DATE10.;
datalines;
1.43410300322e+18 235031 199809 J 1 100781305 1 21 1 19 20150409 02:34:00 20150409 10:50:00 1 20150409 20150409 C6057650 300 R55X J459           0 09APR2015 09APR2015 9 1 1 1 4 14 40 Clinical_laboratory Respiratory Ungrouped Ungrouped Ungrouped Ungrouped
Ungrouped 1
1.43410300322e+18 1211513 201106 99 2 100784128 1 21 1 19 20150416 04:00:00 20150416 02:45:00 1 20150416 20150416 C6101983 420 B349 R062           1 16APR2015 16APR2015 2 1 1 1 4 14 40 Infections Clinical_laboratory Ungrouped Ungrouped Ungrouped Ungrouped
Ungrouped 1
1.43410300322e+18 108097 200407 J 2 100781392 1 21 1 19 20150409 07:30:00 20150409 03:35:00 1 20150409 20150409 C4340568 160 S611 W239           0 09APR2015 09APR2015 15 1 1 1 4 14 40 Injuries Injuries Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped 1
1.43410300322e+18 606892 201304 A 1 100787788 1 22 1 19 20150427 01:00:00 20150429 02:30:00 1 20150427 20150429 C3546411 420 J181 Q909 Z931 E835       0 27APR2015 29APR2015 16 1 1 1 4 14 40 Respiratory Congenital Potential_hazard Endocrine Ungrouped
Ungrouped Ungrouped 1
1.43410300322e+18 1084927 200910 S 1 100786427 1 28 1 19 20150423 08:00:00 20150423 04:16:00 1 20150423 20150423 C4673523 160 L030 B956           0 23APR2015 23APR2015 10 1 1 1 4 14 40 Subcutanous Infections Ungrouped Ungrouped Ungrouped Ungrouped
Ungrouped 1
1.43410300322e+18 391210 200811 99 1 100783594 1 28 1 19 20150414 10:30:00 20150415 04:00:00 1 20150414 20150415 C6145391 145 K047             0 14APR2015 15APR2015 1 1 1 1 4 14 40 Digestive Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped 0
1.43410300322e+18 311756 200311 J 1 100786148 1 22 1 19 20150422 11:15:00 20150423 10:25:00 1 20150422 20150423 C3413878 100 K566 Q250 I272 Q251 Q336 H905   1 22APR2015 23APR2015 7 1 1 1 4 14 40 Digestive Congenital Ungrouped Congenital Congenital
Ear_mastoid Ungrouped 1
1.43410300322e+18 326905 199905 A 1 100784971 1 28 1 19 20150419 09:00:00 20150419 05:30:00 1 20150419 20150419 C2611493 160 S618 W260           1 19APR2015 19APR2015 13 1 1 1 4 14 40 Injuries Injuries Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped 1
1.43410300322e+18 1118340 201003 A 2 100785020 1 22 1 19 20150419 06:50:00 20150420 03:44:00 1 20150419 20150420 C4217176 100 N390             0 19APR2015 20APR2015 7 1 1 1 4 14 40 Genitourinary Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped 1
1.43410300322e+18 538555 200404 J 1 100799443 1 28 1 19 20150416 10:30:00 20150416 04:00:00 1 20150416 20150416 C3451324 420 N390 N270 N288 J459 Z936 Z933   1 16APR2015 16APR2015 1 1 1 1 4 14 40 Genitourinary Genitourinary Genitourinary Respiratory
Potential_hazard Potential_hazard Ungrouped 0
;
run;

Can you please try now.

Ta

[PaigeMiller]

Its not reading the data properly.

Please look at your own SAS data set and see if it is reading the data properly, particularly the variables of interest new_code through sixth_code.

[sks521]

Hi,

It is reading the data properly. Wasn't reading before but I made a minor change and then pasted on here;

data restrict;
  infile datalines dsd truncover;

input apc1_apc2_link:$32. pkid:$32. dateofbirth:$8. ethnicgroup:$2. sex:$1. hospitalproviderspellnumber:$32. 
administrativecategory:$1. admissionmethodhospitalproviders:$2. dischargemethodhospitalproviders:$1. sourceofadmissionhospitalprovide:$2. startdate
hospitalproviderspell:$8. starttimehospitalproviderspell:$8. dischargedatefromhospitalprovide:$8. 
dischargetimehospitalproviderspe:$8. episodenumber:$1. startdateconsultantepisode:$8. enddateconsultantepisode:$8. consultantcode:$8. mainspecialtycode:$3. di
agnosisprimaryicd:$6. diagnosis1stsecondaryicd:$6. diagnosis2ndsecondaryicd:$6. diagnosis3rdsecondaryicd:$6. 
diagnosis4thsecondaryicd:$6. diagnosis5thsecondaryicd:$6. diagnosis6thsecondaryicd:$6. hospitalspellduration:$1. Start_date:DATE10. Dis_date:DATE10.
age:32. VISIT:32. age1:32. admission:32. YEAR:32. quarter:32. month:32. New_code:$6. First_code:$6. Second_code:$6. 
Third_code:$6. Fourth_code:$6. Fifth_code:$6. Sixth_code:$6. age2:32.;
  format Start_date DATE10. Dis_date DATE10.;
datalines;
1.43410300322e+18 235031 199809 J 1 100781305 1 21 1 19 20150409 02:34:00 20150409 10:50:00 1 20150409 20150409 C6057650 300 R55X J459           0 09APR2015 09APR2015 9 1 1 1 4 14 40 Clinical_laboratory Respiratory Ungrouped Ungrouped Ungrouped Ungrouped
Ungrouped 1
1.43410300322e+18 1211513 201106 99 2 100784128 1 21 1 19 20150416 04:00:00 20150416 02:45:00 1 20150416 20150416 C6101983 420 B349 R062           1 16APR2015 16APR2015 2 1 1 1 4 14 40 Infections Clinical_laboratory Ungrouped Ungrouped Ungrouped Ungrouped
Ungrouped 1
1.43410300322e+18 108097 200407 J 2 100781392 1 21 1 19 20150409 07:30:00 20150409 03:35:00 1 20150409 20150409 C4340568 160 S611 W239           0 09APR2015 09APR2015 15 1 1 1 4 14 40 Injuries Injuries Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped 1
1.43410300322e+18 606892 201304 A 1 100787788 1 22 1 19 20150427 01:00:00 20150429 02:30:00 1 20150427 20150429 C3546411 420 J181 Q909 Z931 E835       0 27APR2015 29APR2015 16 1 1 1 4 14 40 Respiratory Congenital Potential_hazard Endocrine Ungrouped
Ungrouped Ungrouped 1
1.43410300322e+18 1084927 200910 S 1 100786427 1 28 1 19 20150423 08:00:00 20150423 04:16:00 1 20150423 20150423 C4673523 160 L030 B956           0 23APR2015 23APR2015 10 1 1 1 4 14 40 Subcutanous Infections Ungrouped Ungrouped Ungrouped Ungrouped
Ungrouped 1
1.43410300322e+18 391210 200811 99 1 100783594 1 28 1 19 20150414 10:30:00 20150415 04:00:00 1 20150414 20150415 C6145391 145 K047             0 14APR2015 15APR2015 1 1 1 1 4 14 40 Digestive Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped 0
1.43410300322e+18 311756 200311 J 1 100786148 1 22 1 19 20150422 11:15:00 20150423 10:25:00 1 20150422 20150423 C3413878 100 K566 Q250 I272 Q251 Q336 H905   1 22APR2015 23APR2015 7 1 1 1 4 14 40 Digestive Congenital Ungrouped Congenital Congenital
Ear_mastoid Ungrouped 1
1.43410300322e+18 326905 199905 A 1 100784971 1 28 1 19 20150419 09:00:00 20150419 05:30:00 1 20150419 20150419 C2611493 160 S618 W260           1 19APR2015 19APR2015 13 1 1 1 4 14 40 Injuries Injuries Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped 1
1.43410300322e+18 1118340 201003 A 2 100785020 1 22 1 19 20150419 06:50:00 20150420 03:44:00 1 20150419 20150420 C4217176 100 N390             0 19APR2015 20APR2015 7 1 1 1 4 14 40 Genitourinary Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped Ungrouped 1
1.43410300322e+18 538555 200404 J 1 100799443 1 28 1 19 20150416 10:30:00 20150416 04:00:00 1 20150416 20150416 C3451324 420 N390 N270 N288 J459 Z936 Z933   1 16APR2015 16APR2015 1 1 1 1 4 14 40 Genitourinary Genitourinary Genitourinary Respiratory
Potential_hazard Potential_hazard Ungrouped 0
;
run;

[PaigeMiller]

Okay, we're spinning our wheels here and achieving nothing. The code isn't working. It's not your fault, but it isn't working.

So, please, we have asked twice already, LOOK AT your data in the SAS data set (not in any other appearance) and see if the variables new_code through sixth_code are appearing properly as you expect them to appear. 

[ed_sas_member]

Hi @sks521 

The match making is case sensitive, that's why 'respiratory' is not 'Respiratory', ...

You can either adapt the case in your code or use the following one:

data respiratory;
	set restrict;
	array _codes (*) _character_; /*_character_ references all character variable, but you can also put the variable names*/
	respire = 0;
	do j=1 to dim(_codes);
		if prxmatch('/respiratory|clinical_laboratory/i',_codes(j)) then do; /* the i modifier specifies 'case insensitive'*/
			respire = 1;
			leave;
		end;
	end;
	drop j;
run;

[sks521]

How do I put in multiple variable names please?

[ed_sas_member]

Hi @sks521 

You can do this:

data respiratory;
	set restrict;
	array _codes (*) New_code First_code Second_code Third_code;
	respire = 0;
	do j=1 to dim(_codes);
		if prxmatch('/respiratory|clinical_laboratory/i',_codes(j)) then do; /* the i modifier specifies 'case insensitive'*/
			respire = 1;
			leave;
		end;
	end;
	drop j;
run;

[sks521]

Still doesn't solve the problem, I'm afraid! The new variable has 0 values for all obs.

[sks521]

I am still getting 0 for all obs.