DATA Step, Macro, Functions and more

creating a variable depending on the order of the date (of the event)

Reply
Contributor
Posts: 24

creating a variable depending on the order of the date (of the event)

Hello all,

 

I am trying to find out the problem with creating a variable depending on the order of the date (of the event)

 

I have the dataset of with the patients who were first screened for cancer.

 

Variables:

ID=patient ID 

obs_st=date of the cancer screening (yymmddn8 format)

any_c_date=date of the first cancer dignosis (yymmddn8 format)

fr_1yr_idx=1 year after the first cancer screening date (yymmddn8 format)

 

when I used the following command to create a variable to identify the patients who had dignosis of cancer within the first year after

cancer screening date, it appears to be not working...

 

data work.c_questions; set work.c_questions;
if any_c_date<fr_1yr_idx then exc_1yr=1; else exc_1yr=0;
run;

It looks like all values for the variable exc_1yr is set to 0. 

 

I am confused about what I am doing wrong here because when I ran a test with different dataset, it worked fine as below...

/***creating variable depending on the order of the date*/
data work.cardinfo;                                                                                                                     
length ID $4 FR_CD_DT $8 PC_FR_DT $8;                                                                                                   
input ID $ FR_CD_DT $ PC_FR_DT;                                                                                                         
cards;                                                                                                                                  
1001 20040504 20050101                                                                                                                  
1002 20100102 20081205                                                                                                                  
;run;                                                                                                                                   
                                                                                                                                        
data work.cardinfo; set work.cardinfo;                                                                                                  
first_card_date=input(put(FR_CD_DT,8.),best8.);format first_card_date yymmddn8.;                                                        
pc_first_date=input(put(PC_FR_DT,8.),best8.);format pc_first_date yymmddn8.;                                                            
run;                                                                                                                                    
                                                                                                                                        
data work.cardinfo; set work.cardinfo;                                                                                                  
if first_card_date<pc_first_date then bfc=1; else bfc=0;                                                                                
run;                                                                                                                                    
                                         

Can anyone please help me with this?

 

Thank you,

 

Sincerely,

 

Maria. 


c_question.jpg
Super User
Posts: 7,763

Re: creating a variable depending on the order of the date (of the event)

Posted in reply to sasworker16

Something else seems to be going on.

A quick check with data taken from your jpg, both with character variables and SAS date values:

data have;
input ID $ obs_st $ any_c_date $ fr_1yr_idx $;
cards;
10556123 20070722 20080201 20080721
;
run;

data want;
set have;
if any_c_date < fr_1yr_idx
then bfc1 = 1;
else bfc1 = 0;
run;


proc print data=want noobs;
run;

data have2;
input ID $ obs_st :yymmdd8. any_c_date :yymmdd8. fr_1yr_idx :yymmdd8.;
format obs_st any_c_date fr_1yr_idx yymmddn8.;
cards;
10556123 20070722 20080201 20080721
;
run;

data want2;
set have2;
if any_c_date < fr_1yr_idx
then bfc1 = 1;
else bfc1 = 0;
run;

proc print data=want2 noobs;
run;

The result:

                         any_c_     fr_1yr_
   ID        obs_st       date        idx       bfc1

10556123    20070722    20080201    20080721      1 
                                                    

                          any_c_     fr_1yr_
   ID         obs_st        date         idx    bfc1

10556123    20070722    20080201    20080721      1 

As you can see, bfc1 was correctly set in both cases.

Look at the attributes of columns any_c_date and fr_1yr_idx.

Also post the log of the data step in which you set bfc1; use the {i} icon to preserve formatting.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: creating a variable depending on the order of the date (of the event)

Posted in reply to sasworker16

Hi,

 

Well, logic working on data you know is right = solution is your data doesn't match the test data provided.  Provide a datastep of your data, from what you have, using this post:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

Its likely to be something silly, but I can't tell from the test data you posted.

 

Also, your code there, most of it is noot necessary - updated with a bit of formatting:

data work.cardinfo;
  informat fr_cd_dt pc_fr_dt yymmdd10.; 
  length id $4;                                                                                                   
  input id $ fr_cd_dt pc_fr_dt;
  bfc=ifn(fr_cd_dt < pc_fr_dt,1,0);                                                                                
  format fr_cd_dt pc_fr_dt yymmdd10.; 
cards;                                                                                                                                  
1001 20040504 20050101                                                                                                                  
1002 20100102 20081205                                                                                                                  
;
run   
Contributor
Posts: 24

Re: creating a variable depending on the order of the date (of the event)

Thank you for your advice.

 

All of my date variables have the following format:

Length: 8

Format: yymmddn8.

Informat: 8.

 

I keep getting an error message saying

NOTE: Table has been opened in browse mode.
ERROR: There was a problem with the format so BEST. was used

 

l have tried the following for all of my date variables, which was suggested by a previous post

data work.c_question; set work.c_question;

obs_st=datepart(obs_st);
put obs_st mmddyyn8.;

run;

 

https://communities.sas.com/t5/SAS-Procedures/Problem-when-formatting-a-date-variable/td-p/109957

to fix this problem, but does not seem to be working for me..

 

 

 

 

 

 

Super User
Posts: 7,763

Re: creating a variable depending on the order of the date (of the event)

Posted in reply to sasworker16

sasworker16 wrote:

Thank you for your advice.

 

All of my date variables have the following format:

Length: 8

Format: yymmddn8.

Informat: 8.

 

I keep getting an error message saying

NOTE: Table has been opened in browse mode.
ERROR: There was a problem with the format so BEST. was used

 

l have tried the following for all of my date variables, which was suggested by a previous post

data work.c_question; set work.c_question;

obs_st=datepart(obs_st);
put obs_st mmddyyn8.;

run;

 

https://communities.sas.com/t5/SAS-Procedures/Problem-when-formatting-a-date-variable/td-p/109957

to fix this problem, but does not seem to be working for me..

 


This ERROR message from the viewtable window usually shows that you have numbers that can't be reasonably displayed with a date format.

Like the number 20170426, which is not a valid SAS date value. The SAS date value for 2017-04-26 is 20935 (number of days since 1960-01-01).

So your problem starts when you import the data into SAS. Review that process, and restructure it in a way that proper SAS data values are created from the input data.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: creating a variable depending on the order of the date (of the event)

Posted in reply to sasworker16

Yes, really need to see the data - follow that link I sent you, it create a plain text version of your data which you can post here.  Its likely that the data doesn't conform to the yymmddn8. format, probably because at least one is date/time.

If you do:

data want;
  set have;
  format _all_;
  informat _all_;
run;

This will remove all formatting, you will see that datetime values are lot bigger than date values.

Super User
Posts: 7,763

Re: creating a variable depending on the order of the date (of the event)

Posted in reply to sasworker16

Oh yeah, and you have not posted any logs. "The log" means the whole section of your log that includes the complete step code and the messages belonging to that step. Use the {i} icon for posting that to preserve the formatting.

 

Maxim 2: Read the log. It contains everything you need to know, albeit sometimes in a not-so-easily-to-interpret form. But the experts here can help you to decipher that.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 6 replies
  • 138 views
  • 0 likes
  • 3 in conversation