BookmarkSubscribeRSS Feed
sasworker16
Calcite | Level 5

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
6 REPLIES 6
Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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   
sasworker16
Calcite | Level 5

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..

 

 

 

 

 

 

Kurt_Bremser
Super User

@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.

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 945 views
  • 0 likes
  • 3 in conversation