conditional statements depending on whether consecutive variable exists

Reply
Frequent Contributor
Frequent Contributor
Posts: 133

conditional statements depending on whether consecutive variable exists

[ Edited ]

Hello, 

I have transposed a file which will have different variable names but in sequential order. I need to use these variables for conditional statements only if they exist. The variables go from start1 to start13 . The data from start1 - start3_02 looks like this:

 

data=Have - sample data 

 

Data have ;
Infile datalines dlm=',' dsd;
informat ID 8. Date mmddyy10. Value 8.2 start1 mmddyy10. start1_01 mmddyy10. start1_02 mmddyy10. start2 mmddyy10. start2_01 mmddyy10. start2_02 mmddyy10.
start3 mmddyy10. start3_01 mmddyy10. start3_02 mmddyy10. end1 mmddyy10. end1_01 mmddyy10.
end1_02 mmddyy10. end2 mmddyy10. end2_01 mmddyy10. end2_02 mmddyy10. end3 mmddyy10. end3_01 mmddyy10. end3_02 mmddyy10.;

format ID 8. Date mmddyy10. Value 8.2 start1 mmddyy10. start1_01 mmddyy10. start1_02 mmddyy10. start2 mmddyy10. start2_01 mmddyy10. start2_02 mmddyy10.
start3 mmddyy10. start3_01 mmddyy10. start3_02 mmddyy10. end1 mmddyy10. end1_01 mmddyy10.
end1_02 mmddyy10. end2 mmddyy10. end2_01 mmddyy10. end2_02 mmddyy10. end3 mmddyy10. end3_01 mmddyy10. end3_02 mmddyy10.;
input ID Date Value start1 start1_01 start1_02 start2 start2_01 start2_02
start3 start3_01 start3_02 end1 end1_01
end1_02 end2 end2_01 end2_02 end3 end3_01 end3_02 ;

datalines ;
1001,4/15/2015,683,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,7/15/2015,768,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,8/5/2015,637,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,9/23/2015,368,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,10/28/2015,356,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,11/9/2015,43,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,12/9/2015,386,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,1/20/2016,361,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,2/17/2016,463,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,3/9/2016,45,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,4/21/2016,608,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1005,12/16/2013,1842,12/17/2013,,,3/8/2016,,,,,,3/4/2014,,,3/22/2016,,,,,
1005,1/14/2014,543,12/17/2013,,,3/8/2016,,,,,,3/4/2014,,,3/22/2016,,,,,
1008,8/4/2014,55,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,8/25/2014,11,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,9/29/2014,6,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,10/27/2014,6,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,11/24/2014,5,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,12/15/2014,7,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,1/12/2015,5,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,2/23/2015,12,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,4/20/2015,114,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1010,4/9/2015,1019,8/27/2015,,,,,,,,,11/5/2015,,,,,,,,
1010,7/9/2015,328,8/27/2015,,,,,,,,,11/5/2015,,,,,,,,
1010,1/14/2016,2346,8/27/2015,,,,,,,,,11/5/2015,,,,,,,,
1010,3/3/2016,4948,8/27/2015,,,,,,,,,11/5/2015,,,,,,,,
1010,4/14/2016,4599,8/27/2015,,,,,,,,,11/5/2015,,,,,,,,
1010,5/12/2016,4095,8/27/2015,,,,,,,,,,,,,,,,,
;
proc print; run;

 

Since this will be run at different times with different variables (someday _03 or _04 might be added) I thought I could create a table from a proc contents to define the variables that will be used and perhaps use this as a hash object or do loop. The hash variables then could be used in conditional statements to create intervalLot which is the same as the suffix of the start variable.

 


proc contents data=have out=startContents varnum ;
run;
proc sort data=startContents out=startLookup (keep=name varnum where=(substr(name,1,5)='start'));
by varnum ;
run;

The SAS System 
   
ObsNAMEVARNUM
1start16
2start1_017
3start1_028
4start29
5start2_0110
6start2_0211
7start312
8start3_0113
9start3_0214
10start415
11start4_0116
12start517
13start5_0118
14start619
15start720
16start821
17start8_0122
18start923
19start1024
20start1125
21start1226
22start1327

 

 

the old syntax is below but I want to figure out how to change the startn variables to determine if they exist because while some records have start1_01 some skip directly to start2. IntervalLot is first defined as the same suffix of the start variable if the date is between the start/end pairs with the same suffix. Next it looks at the difference from the prior non-missing end variable with the same name and the current start variable  to find out if the date is between start periods. To keep the variables numeric I am just adding a .98 to indicate an interval between startperiods rather than between start/end pairs. 

 

 

Data WANT ;

set HAVE ;
by ID;
format intervalLot 8.2 ;


if date < start1 then intervalLot='0.98' ;
else if date >= start1 and date < end1  then intervalLot='1' ;

else if date >=start1_01  and date < end1_01  then intervallot = '1_01'

else if date >=start1_02  and date < end1_02  then intervallot = '1_02'

....

else if date > end1_02 and date <start2 then intervalLot='1.982'

else if date > end1_01 and date <start2 then intervalLot='1.981'

else if date > end1 and date < start2 then intervalLot='1.98' ;


..

 

Any suggestions would be very much appreciated. 

Thanks.

 

 

Super User
Super User
Posts: 7,682

Re: conditional statements depending on whether consecutive variable exists

Well, to be honest don't.  It is far easier to work with normalised data (goes down the page) rather than transposed.  the post you have given is across the page and such like so can't read it.  If you can post a simple datastep with some test data and what you want out of it then we will be able to provide some code.

Frequent Contributor
Frequent Contributor
Posts: 133

Re: conditional statements depending on whether consecutive variable exists

The simple datastep isn't so simple for me. Here is the data separated by comma's but the code is not working. I haven't done much with datalines or date formats but maybe you can figure it out with the data. There should be missing data between consecutive commas. 

 

Data have ;
Infile datalines dsd ;
Input ID 8. Date mmddyy10. Value 8.2 start1 mmddyy10.start1_01 mmddyy10. start1_02 mmddyy10.start2 mmddyy10.start2_01 mmddyy10.start2_02 mmddyy10.
start3 mmddyy10.start3_01 mmddyy10.start3_02 mmddyy10.end1 mmddyy10.end1_01 mmddyy10.
end1_02 mmddyy10.end2 mmddyy10.end2_01 mmddyy10.end2_02 mmddyy10.end3 mmddyy10.end3_01 mmddyy10.end3_02 mmddyy10.;
datalines ;
1001,4/15/2015,683,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,7/15/2015,768,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,8/5/2015,637,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,9/23/2015,368,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,10/28/2015,356,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,11/9/2015,43,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,12/9/2015,386,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,1/20/2016,361,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,2/17/2016,463,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,3/9/2016,45,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1001,4/21/2016,608,4/14/2003,7/15/2015,,9/30/2015,,,,,,5/23/2003,8/26/2015,,5/18/2016,,,,,
1005,12/16/2013,1842,12/17/2013,,,3/8/2016,,,,,,3/4/2014,,,3/22/2016,,,,,
1005,1/14/2014,543,12/17/2013,,,3/8/2016,,,,,,3/4/2014,,,3/22/2016,,,,,
1008,8/4/2014,55,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,8/25/2014,11,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,9/29/2014,6,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,10/27/2014,6,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,11/24/2014,5,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,12/15/2014,7,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,1/12/2015,5,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,2/23/2015,12,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1008,4/20/2015,114,7/14/2014,,,12/15/2014,,,4/27/2015,,,12/8/2014,,,1/12/2015,,,7/6/2015,,
1010,4/9/2015,1019,8/27/2015,,,,,,,,,11/5/2015,,,,,,,,
1010,7/9/2015,328,8/27/2015,,,,,,,,,11/5/2015,,,,,,,,
1010,1/14/2016,2346,8/27/2015,,,,,,,,,11/5/2015,,,,,,,,
1010,3/3/2016,4948,8/27/2015,,,,,,,,,11/5/2015,,,,,,,,
1010,4/14/2016,4599,8/27/2015,,,,,,,,,11/5/2015,,,,,,,,
1010,5/12/2016,4095,8/27/2015,,,,,,,,,,,,,,,,,
;
proc print; run;

 

 

Super User
Posts: 11,105

Re: conditional statements depending on whether consecutive variable exists

Many typos,  at least when copied and pasted from the post.

Things like

start3_01 mmddyy10.start3_02 are errors because the variables need a space between the previous variable format

start3_01 mmddyy10. start3_02.

 

Also there appear to be, at least when copying from the forum "invisible" characters in the posted data. So there are lots of errors generated.

Because of the way formats on an input statement are used you might want to try:

Data have ;
   Infile datalines dlm=',' ;
   informat ID 8. Date mmddyy10. Value 8.2 start1 mmddyy10. start1_01 mmddyy10. start1_02 mmddyy10. start2 mmddyy10. start2_01 mmddyy10. start2_02 mmddyy10.
   start3 mmddyy10. start3_01 mmddyy10. start3_02 mmddyy10. end1 mmddyy10. end1_01 mmddyy10.
   end1_02 mmddyy10. end2 mmddyy10. end2_01 mmddyy10. end2_02 mmddyy10. end3 mmddyy10. end3_01 mmddyy10. end3_02 mmddyy10.;

   format ID 8. Date mmddyy10. Value 8.2 start1 mmddyy10. start1_01 mmddyy10. start1_02 mmddyy10. start2 mmddyy10. start2_01 mmddyy10. start2_02 mmddyy10.
   start3 mmddyy10. start3_01 mmddyy10. start3_02 mmddyy10. end1 mmddyy10. end1_01 mmddyy10.
   end1_02 mmddyy10. end2 mmddyy10. end2_01 mmddyy10. end2_02 mmddyy10. end3 mmddyy10. end3_01 mmddyy10. end3_02 mmddyy10.;
   input ID  Date  Value start1 start1_01 start1_02 start2 start2_01 start2_02 
   start3 start3_01 start3_02 end1 end1_01 
   end1_02 end2 end2_01 end2_02 end3 end3_01 end3_02 ;

 

to read your data.

Look in the log for errors with ? appearing the data lines. If you see those, then you need to manually delet them. Hopefully those are a side affect of posting here.

Frequent Contributor
Frequent Contributor
Posts: 133

Re: conditional statements depending on whether consecutive variable exists

Thanks. Your code works with dsd added so the dates align right. I appreciate you taking the time to fix this .

Super User
Posts: 9,856

Re: conditional statements depending on whether consecutive variable exists

Once you get variable names via
 proc contents data=have out=startContents varnum ;
run;


you can use CALL EXECUTE to do what you want.




Ask a Question
Discussion stats
  • 5 replies
  • 350 views
  • 0 likes
  • 4 in conversation