topic Re: Date not converting to a SAS date in SAS Programming

Date not converting to a SAS date

Blndbrm727 — Wed, 08 Jan 2020 20:29:29 GMT

Hi All,

I am creating the uniqueid as I will be merging this file with another that has the uniqueid already
created with the MRN and the SAS date. Not sure why it is not creating in this file. Prior to this code
I am importing Excel files then doing proc transpose. Not sure if that is affecting it or not.
Any suggestions? Thanks! Susan


Data LBCFin3;
      set LBCFin3;
	  Format ALIAS2 30.;
      If ALIAS2=. then delete;
	  Rename ALIAS2=MRN;
      ACCESS=substr(ACCESSION,10,9);
    run;

Data LBCFin4;
	Set LBCFin3;
uniqueID = compress (MRN||DRAWN_DT_TM); 
run;

EXAMPLE :

uniqueID:

285210/15/19

19351107/10/19

23411807/21/19

Re: Date not converting to a SAS date

Reeza — Wed, 08 Jan 2020 20:32:03 GMT

I've cleaned up some of your post to make it more legible. It would help to show what you have and what you expect as output. We see your code but not knowing what you want I have no idea why it's wrong or if it is even wrong.

I would recommend you do not code in the fashion where the SET and DATA statement have the same names (your first data step). It makes it harder to debug issues.

I would also recommend you apply a Z format to your MRN number and date so you always have the same length of the ID which helps when merging. Otherwise a trailing 0 could be from the MRN or the date?

OH, COMPRESS() is not the function you want, you want CATT() most likely instead.

uniqueID = catt(put(MRN, Z10.), put(drawn_dt_tm, yymmdd10.));

Re: Date not converting to a SAS date

Tom — Wed, 08 Jan 2020 20:41:21 GMT

This line does not look right.

Format ALIAS2 30.;

SAS can only store about 15 decimal digits precisely. So if ALIAS2 is a number than really has 30 digits you will not be able to use it. And if the ALIAS2 is really a character variable then you should be attaching a character format to it instead of numeric one.

Format ALIAS2 $30.;

Re: Date not converting to a SAS date

Blndbrm727 — Thu, 09 Jan 2020 00:28:50 GMT

Hi Reeza,

To answer you question about what I am getting versus what I want;

Output: Desired output

123457/14/19 1234521550 (MRN+SAS numeric date)

Hope that helps!

Thanks!

Susan

Re: Date not converting to a SAS date

Kurt_Bremser — Thu, 09 Jan 2020 06:13:48 GMT

PLEASE post source data in usable form (data step with datalines). Without a clear picture of the data that you have, it's near impossible to provide a working answer.

No item in your previous post relates to this:

@Blndbrm727 wrote:

123457/14/19

Re: Date not converting to a SAS date

Blndbrm727 — Thu, 09 Jan 2020 14:07:15 GMT

Data LBCFin;

Input ALIAS2 DRAWN_DT_TM CE_EVENT_DISP $ RESULT_VAL $;

Datalines;

9023665 09/09/19 Tramadol Negative

9023665 09/09/19 Oxymorphone Negative

9023665 09/09/19 Oxycodone Negative

9023665 09/09/19 Norfentanyl Positive

9023665 09/09/19 Morphine Negative

539151 09/10/19 Tramadol Negative

539151 09/10/19 Oxymorphone Negative

539151 09/10/19 Oxycodone Negative

539151 09/10/19 Norfentanyl Positive

539151 09/10/19 Morphine Negative

30000180858 09/06/19 Tramadol Negative

30000180858 09/06/19 Oxymorphone Positive

30000180858 09/06/19 Oxycodone Negative

30000180858 09/06/19 Norfentanyl Positive

30000180858 09/06/19 Morphine Positive

9146000 09/09/19 Tramadol Negative

9146000 09/09/19 Oxymorphone Negative

9146000 09/09/19 Oxycodone Negative

9146000 09/09/19 Norfentanyl Positive

9146000 09/09/19 Morphine Negative

10009041914 08/03/19 Tramadol Negative

10009041914 08/03/19 Oxymorphone Negative

10009041914 08/03/19 Oxycodone Negative

10009041914 08/03/19 Norfentanyl Positive

10009041914 08/03/19 Morphine Negative

10007219967 08/20/19 Tramadol Negative

10007219967 08/20/19 Oxymorphone Negative

10007219967 08/20/19 Oxycodone Negative

10007219967 08/20/19 Norfentanyl Positive

9300271 08/27/19 Tramadol Negative

9300271 08/27/19 Oxymorphone Negative

9300271 08/27/19 Oxycodone Negative

9300271 08/27/19 Norfentanyl Positive

9300271 08/27/19 Morphine Negative

10008038267 08/16/19 Tramadol Positive

10008038267 08/16/19 Oxymorphone Negative

10008038267 08/16/19 Oxycodone Negative

10008038267 08/16/19 Norfentanyl Positive

10008038267 08/16/19 Morphine Negative

70000381170 08/13/19 Tramadol Negative

70000381170 08/13/19 Oxymorphone Negative

70000381170 08/13/19 Oxycodone Negative

70000381170 08/13/19 Norfentanyl Positive

70000381170 08/13/19 Morphine Negative

10009130527 08/10/19 Tramadol Negative

10009130527 08/10/19 Oxymorphone Negative

10009130527 08/10/19 Oxycodone Negative

10009130527 08/10/19 Norfentanyl Positive

10009130527 08/10/19 Morphine Negative

10014965503 08/13/19 Tramadol Negative

10014965503 08/13/19 Oxymorphone Negative

10014965503 08/13/19 Oxycodone Negative

10014965503 08/13/19 Norfentanyl Positive

10014965503 08/13/19 Morphine Negative

30000464968 08/15/19 Tramadol Positive

30000464968 08/15/19 Oxymorphone Negative

30000464968 08/15/19 Oxycodone Positive

30000464968 08/15/19 Norfentanyl Negative

30000464968 08/15/19 Morphine Positive

70000381444 08/14/19 Tramadol Negative

70000381444 08/14/19 Oxymorphone Negative

70000381444 08/14/19 Oxycodone Negative

70000381444 08/14/19 Norfentanyl Positive

70000381444 08/14/19 Morphine Negative

70000380987 08/12/19 Tramadol Negative

70000380987 08/12/19 Oxymorphone Negative

70000380987 08/12/19 Oxycodone Negative

70000380987 08/12/19 Norfentanyl Positive

70000380987 08/12/19 Morphine Positive

102686961 08/15/19 Tramadol Negative

102686961 08/15/19 Oxymorphone Negative

102686961 08/15/19 Oxycodone Negative

102686961 08/15/19 Norfentanyl Positive

102686961 08/15/19 Morphine Positive

102066046 08/15/19 Tramadol Negative

102066046 08/15/19 Oxymorphone Negative

102066046 08/15/19 Oxycodone Negative

102066046 08/15/19 Norfentanyl Negative

102066046 08/15/19 Morphine Negative

102664250 08/15/19 Tramadol Negative

102664250 08/15/19 Oxymorphone Negative

102664250 08/15/19 Oxycodone Negative

102664250 08/15/19 Norfentanyl Positive

102664250 08/15/19 Morphine Positive

105935989 08/17/19 Tramadol Negative

105935989 08/17/19 Oxymorphone Negative

105935989 08/17/19 Oxycodone Negative

105935989 08/17/19 Norfentanyl Negative

105935989 08/17/19 Morphine Negative

102098379 08/21/19 Tramadol Negative

102098379 08/21/19 Oxymorphone Negative

102098379 08/21/19 Oxycodone Negative

102098379 08/21/19 Norfentanyl Positive

102024174 08/22/19 Tramadol Negative

102024174 08/22/19 Oxymorphone Negative

102024174 08/22/19 Oxycodone Negative

102024174 08/22/19 Norfentanyl Positive

102024174 08/22/19 Morphine Negative

;

Run;

CODE:

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

/*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 ACCESSION;
run;

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

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

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

proc print data=LBCFin3; run;

Data LBCFin3;
set LBCFin3;
Format ALIAS2 30.;
If ALIAS2=. then delete;
Rename ALIAS2=MRN;
ACCESS=substr(ACCESSION,10,9);
run;

Data LBCFin4;
Set LBCFin3;
uniqueID = compress (MRN||DRAWN_DT_TM);
run;

proc print data=LBCFin4; run;

Re: Date not converting to a SAS date

Kurt_Bremser — Thu, 09 Jan 2020 14:18:16 GMT

You try to use variable ACCESSION, but it is not in your dataset. I also suspect that DRAWN_DT_TM should be a date, so the data step code should be

Data LBCFin;
Input ALIAS2 DRAWN_DT_TM :mmddyy10. CE_EVENT_DISP $ RESULT_VAL $;
format DRAWN_DT_TM yymmddd10.;
Datalines;

Re: Date not converting to a SAS date

Tom — Thu, 09 Jan 2020 15:11:33 GMT

Please be very specific about what you are trying to do. What inputs. What you want as outputs. Provide examples. If your current code is not working explain how it is wrong.

Your example data step does not look right. ALIAS2 should be a character variable, not a number. DRAWN_DT_TM is not a number. You will need to either read it as a string or use an informat to convert it to a date. Note that you should NOT be using only two digits for the year in your date strings.

If you want to convert 'Negative' to 0 and 'Positive' to 1 you should probably use an INFORMAT and not a format.