topic Re: Code for New Variables not producing output - Using If-Then statements in SAS Programming

Code for New Variables not producing output - Using If-Then statements

Blndbrm727 — Fri, 13 Dec 2019 20:58:18 GMT

Hello All,

I am stumped on what is not working here. My goal is to code the LBCFent variable as positive if either Fental or Norfentanyl are positive (=1). The output for the Proc Freq is good for both Fentnyl and Norfentanyl but the LBCFent is completely empty. Any suggestions?

data LBCFin4;
 set LBCFin3;
 length LBCFent $50.; 
 format LBCFent $50.;
 Fentanyl =strip(Fentanyl);
 Norfentanyl=strip(Norfentanyl);
 Oxycodone = strip(Oxycodone);
 Oxymorphone= strip(Oxymorphone);

if  Fentanyl =1 or Norfentanyl =1 then LBCFent = "Pos"; 
run;


proc freq data=LBCFin4; tables Fentanyl Norfentanyl ; run;

Re: Code for New Variables not producing output - Using If-Then statements

PaigeMiller — Fri, 13 Dec 2019 21:03:07 GMT

You don't say whether your variables are character or numeric ... but its reasonable to guess that they are character, in which case you need

if  Fentanyl ='1' or Norfentanyl ='1' then LBCFent = "Pos";

Either that or your variables fentanyl and norfentanyl are never equal to one, did you check that?

Re: Code for New Variables not producing output - Using If-Then statements

Kurt_Bremser — Fri, 13 Dec 2019 21:51:11 GMT

Please post some example data, so we have something to test code against. Use a data step with datalines.

Re: Code for New Variables not producing output - Using If-Then statements

Blndbrm727 — Fri, 13 Dec 2019 22:47:53 GMT

HI there. Sample variables and code in SAS Window.

Fentanyl                Norfentanyl                    MRN
1                            1                                     123
1                            0                                     234
0                            0                                     345
0                            0                                     456
1                            1                                     567
1                            0                                     678
1                            1                                     789
1                            0                                     890
0                            0                                     910
0                            0                                     321
1                            1                                     432
1                            1                                     543
1                            1                                     654
1                            0                                     765
1                            1                                     876
1                            0                                     987
0                            0                                     990

Re: Code for New Variables not producing output - Using If-Then statements

Kurt_Bremser — Fri, 13 Dec 2019 23:03:23 GMT

PLEASE POST EXAMPLE DATA IN A DATA STEP WITH DATALINES.

Otherwise we have no idea about variable types and other attributes like lengths, formats involved, etc.

Help us to help you.

Re: Code for New Variables not producing output - Using If-Then statements

Blndbrm727 — Fri, 13 Dec 2019 23:11:10 GMT

Hi Paige,

Yes the variables are character. Yes I did check the spreadsheet and all variables are coded 0 or 1.

Thanks!

Susan

Re: Code for New Variables not producing output - Using If-Then statements

Blndbrm727 — Fri, 13 Dec 2019 23:15:30 GMT

data LabConf;
   input Fentanyl $ Norfentanyl $ MRN;
   datalines;
1 0 123
1 1 234
1 1 345
0 0 456
0 1 567
1 1 678
0 1 789
1 0 890
1 1 910
0 0 321
1 0 432
0 1 543
1 1 654
0 1 765
0 0 876
0 0 987
;
run;

Re: Code for New Variables not producing output - Using If-Then statements

Kurt_Bremser — Fri, 13 Dec 2019 23:22:55 GMT

Then you should also use string literals in your condition:

if  strip(Fentanyl) = "1" or strip(Norfentanyl) = "1" then LBCFent = "Pos";

or convert everything to numbers:

if  input(Fentanyl,best.) = 1 or input(Norfentanyl,best.) = 1 then LBCFent = "Pos";

If that still does not give you the expected results, please post the log of your code.

Re: Code for New Variables not producing output - Using If-Then statements

Blndbrm727 — Sun, 15 Dec 2019 19:20:11 GMT

Hi Kurt,

It did not work. The proc freq output show 1's for Fentanyl and Norfentanyl but the "Pos" is not there for LBCFent. Any thoughts?

LOG:

74   data LBCFin4;
75    set LBCFin3;
76    length LBCFent $50.;
77    format LBCFent $50.;
78    if  strip(Fentanyl) = "1" or strip(Norfentanyl) = "1" then
78 ! LBCFent = 'Pos';
79    run;

NOTE: There were 191 observations read from the data set WORK.LBCFIN3.
NOTE: The data set WORK.LBCFIN4 has 191 observations and 22 variables.
NOTE: Compressing data set WORK.LBCFIN4 increased size by 100.00
      percent.
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds


80   proc freq data=LBCFin4; tables Fentanyl Norfentanyl LBCFent ; run
80 ! ;

NOTE: There were 191 observations read from the data set WORK.LBCFIN4.
NOTE: PROCEDURE FREQ used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds

Re: Code for New Variables not producing output - Using If-Then statements

Kurt_Bremser — Tue, 17 Dec 2019 06:14:45 GMT

It WORKS.

Proof:

data have;
input Fentanyl $ Norfentanyl $ MRN;
datalines;
1 0 123
1 1 234
1 1 345
0 0 456
0 1 567
1 1 678
0 1 789
1 0 890
1 1 910
0 0 321
1 0 432
0 1 543
1 1 654
0 1 765
0 0 876
0 0 987
;

data want;
set have;
length LBCFent $50.;
format LBCFent $50.;
if  strip(Fentanyl) = "1" or strip(Norfentanyl) = "1" then LBCFent = 'Pos';
run;

proc print data=want noobs;
run;

Result:

Fentanyl    Norfentanyl    MRN    LBCFent

   1             0         123    Pos      
   1             1         234    Pos      
   1             1         345    Pos      
   0             0         456             
   0             1         567    Pos      
   1             1         678    Pos      
   0             1         789    Pos      
   1             0         890    Pos      
   1             1         910    Pos      
   0             0         321             
   1             0         432    Pos      
   0             1         543    Pos      
   1             1         654    Pos      
   0             1         765    Pos      
   0             0         876             
   0             0         987

Re: Code for New Variables not producing output - Using If-Then statements

Blndbrm727 — Tue, 17 Dec 2019 14:50:20 GMT

Good Morning Kurt! 
Thank you so much but my code is still not working. I have attached some sample data and my code up 
and including that point. It makes no sense  to me why it is not working. 
Any insight or suggestions are appreciated! Thanks! Susan

data have;
input MRN $ Fentanyl $ Norfentanyl ;
datalines;
123	Fentanyl	Positive
123	Norfentanyl 	Positive
123	Oxy		Negative
234	Fentanyl	Negative
234	Norfentanyl	Negative
234	Oxy		Positive
345	Fentanyl	Positive
345	Norfentanyl	Negative
345	Oxy		Positive
456	Fentanyl	Negative
456	Norfentanyl	Negative
456	Oxy		Positive
567	Fentanyl	Positive
567	Norfentanyl	Positive
567	Oxy		Positive
679	Fentanyl	Negative
679	Norfentanyl	Positive
679	Oxy		Negative
678	Fentanyl	Negative
678 	Norfentanyl	Positive
678	Oxy		Negative
789	Fentanyl	Positive
789	Norfentanyl	Positive
789	Oxy		Negative
890	Fentanyl	Negative
890	Norfentanyl	Positive
890	Oxy		Positive
134	Fentanyl	Negative
134	Norfentanyl	Negative
134	Oxy		Negative
245	Fentanyl	Positive
245	Norfentanyl	Positive
245	Oxy		Negative
356	Fentanyl	Negative
356	Norfentanyl	Negative
356	Oxy		Negative
467	Fentanyl	Negative
467	Norfentanyl	Positive
467	Oxy		Positive
578	Fentanyl	Negative
578	Norfentanyl	Negative
578	Oxy		Negative
690	Fentanyl	Positive
690	Norfentanyl	Positive
690	Oxy		Negative
321	Fentanyl	Negative
321	Norfentanyl	Negative
321	Oxy		Negative
;

Data LBCFin;
	set work.LB1;
run;

* Create SAS Dataset from Excel File;

OPTIONS COMPRESS = YES; 
options ls=70 ps=50 mergenoby=error;


proc format ;  
 value $RESULT_VAL 'Positive' = '1'
                   'Negative' = '0';
run;

data LBCFin1;
	set LBCFin;
   format RESULT_VAL $RESULT_VAL.; 
run;

* Printout;

proc print data = LBCFin1; run; 

* Transpose Wide Steps; 

proc sort tagsort data = LBCFin1;
      by ALIAS2 CE_EVENT_DISP DRAWN_DT_TM;
run;

proc transpose data = LBCFin1 out = LBConFin;
            by ALIAS2 CE_EVENT_DISP DRAWN_DT_TM;
            var RESULT_VAL;
run;

proc sort tagsort data = LBConFin; * ADDED THIS EXTRA PROC SORT, IT WORKED;
      by ALIAS2 DRAWN_DT_TM;
run;

proc transpose data = LBConFin delimiter=_ out=LBCFin3 (drop=_name_);
            by ALIAS2 DRAWN_DT_TM;
            var col1; 
            id CE_EVENT_DISP;
run;

proc print data=LBCFin3 (obs=200); run;

 proc contents data=LBCFin4; run;

 proc freq data=LBCFin4; tables Fentanyl Norfentanyl LBCFent LBCNorfent ; run; 
/******************* Changing drug class variables*************/;
/* Fix using variable names*/;

data LBCFin4;
 set LBCFin3;
 length LBCFent $50.; 
 format LBCFent $50.;
 if  strip(Fentanyl) = "1" or strip(Norfentanyl) = "1" then LBCFent = 'Pos';
 run;

Re: Code for New Variables not producing output - Using If-Then statements

PaigeMiller — Tue, 17 Dec 2019 15:17:15 GMT

This is different data than you showed in reply #4 or reply #7. No wonder the code provided doesn't work. It is also very confusingly named, so that variable Fentanyl can have values Fentanyl or norfentanyl. But anyway, you never had numbers 0 or 1, you have character strings formatted to zero or one, and to do any type of logic where you test if a variable is equal to a value, you don't test if is equal to the formatted value (because that won't work), you test if it is equal to the unformatted value.

Anyway, with this data, here is some code that will work

data have;
length mrn $16;
input MRN $ Fentanyl :$16. Norfentanyl :$16. ;
datalines;
123	Fentanyl	Positive
123	Norfentanyl 	Positive
123	Oxy		Negative
234	Fentanyl	Negative
234	Norfentanyl	Negative
234	Oxy		Positive
345	Fentanyl	Positive
345	Norfentanyl	Negative
345	Oxy		Positive
456	Fentanyl	Negative
456	Norfentanyl	Negative
456	Oxy		Positive
567	Fentanyl	Positive
567	Norfentanyl	Positive
567	Oxy		Positive
679	Fentanyl	Negative
679	Norfentanyl	Positive
679	Oxy		Negative
678	Fentanyl	Negative
678 	Norfentanyl	Positive
678	Oxy		Negative
789	Fentanyl	Positive
789	Norfentanyl	Positive
789	Oxy		Negative
890	Fentanyl	Negative
890	Norfentanyl	Positive
890	Oxy		Positive
134	Fentanyl	Negative
134	Norfentanyl	Negative
134	Oxy		Negative
245	Fentanyl	Positive
245	Norfentanyl	Positive
245	Oxy		Negative
356	Fentanyl	Negative
356	Norfentanyl	Negative
356	Oxy		Negative
467	Fentanyl	Negative
467	Norfentanyl	Positive
467	Oxy		Positive
578	Fentanyl	Negative
578	Norfentanyl	Negative
578	Oxy		Negative
690	Fentanyl	Positive
690	Norfentanyl	Positive
690	Oxy		Negative
321	Fentanyl	Negative
321	Norfentanyl	Negative
321	Oxy		Negative
;

proc sort data=have;
    by mrn;
run;

data want;
    merge have(where=(fentanyl='Fentanyl') rename=(norfentanyl=posneg1))
		have(where=(fentanyl='Norfentanyl') rename=(norfentanyl=posneg2));
	by mrn;
    if posneg1='Positive' or posneg2='Positive' then lgcfen='Pos';
run;

Re: Code for New Variables not producing output - Using If-Then statements

Kurt_Bremser — Tue, 17 Dec 2019 15:57:12 GMT

I can only say: Maxim 3

Know Your Data includes assigned formats.

And comparisons always use the raw values, not the formatted ones.