This is an example layout of my data table and data:
VAR1 VAR2 VAR3 VAR4 VAR5
1 V6 1 100
V7 5 450
2 V15 23 88
V12 0 99
10 V4 10 101
I need only the rows where VAR2 is null. In my acutal data there are rows that have values in VAR2.
I need to take the rows where VAR2 is null and create if statements in a sas file i'm making. This file will be included as part of a different program.
This is what I have so far:
filename dump "c:\temp.sas" ;
data _null_ ;
set raw ;
where var2 is null ;
file dump ;
put 'if ' Var3 '= ' Var4 'then ' Var3 '= ' Var5 ';' ;
run ;
My only problem is that I need to put the rows that have a value in VAR1 first sorted by the value in VAR3 in my file then put a comment then put the rows where VAR1 is null.
My sas file should look like this:
if V4 = 10 then V4 = 101;
if V6 = 1 then V6 = 100 ;
if V15 = 23 then V15 = 88 ;
/*These are new entries */
if V7 = 5 then V7 = 450 ;
if V12 = 0 then V12 = 99 ;
1. Can this be done in one data step?
2. How can I put the comment /*These are new entries */ only one time if I have multiple rows with no VAR1 value?
Thank you for any help or advice you can give me.
I tried this way and it seems to work but it seems too sloppy and that I can do it in less steps.
data ex;
input @1 var1 $2. @4 var2 $1. @5 var3 $3. @9 var4 $2. @12 var5 $3.;
datalines;
1 V1 10 100
V4 20 200
2 V5 30 300
10 V3 40 400
V2 50 500
;
proc sort data=ex ;
by var3 ;
run ;
data ex_ var_ ;
set ex ;
if var1 = '' then output var_ ;
else output ex_ ;
run ;
filename dump "c:\temp\temp.sas" ;
data _null_ ;
set ex_ ;
where var2 is null ;
file dump ;
put 'if ' var3 '= ' var4 'then ' var3 '= ' var5 ';' ;
run ;
data _null_ ;
set var_ ;
where var2 is null ;
file dump mod ;
if _n_ = 1 then put / '/*THIS IS A TEST*/' ;
put 'if ' var3 '= ' var4 'then ' var3 '= ' var5 ';' ;
run ;
data ex;
input @1 var1 $2. @4 var2 $1. @5 var3 $3. @9 var4 $2. @12 var5 $3.;
datalines;
1 V1 10 100
V4 20 200
2 V5 30 300
10 V3 40 400
V2 50 500
;
proc sort data=ex ;
by var3 ;
run ;
data ex_ var_ ;
set ex ;
if var1 = '' then output var_ ;
else output ex_ ;
run ;
filename dump "c:\temp\temp.sas" ;
data _null_ ;
set ex_ ;
where var2 is null ;
file dump ;
put 'if ' var3 '= ' var4 'then ' var3 '= ' var5 ';' ;
run ;
data _null_ ;
set var_ ;
where var2 is null ;
file dump mod ;
if _n_ = 1 then put / '/*THIS IS A TEST*/' ;
put 'if ' var3 '= ' var4 'then ' var3 '= ' var5 ';' ;
run ;
Thanks for any help
Any ideas?
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.