Date not converting to a SAS date

[Blndbrm727]

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

[Reeza]

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

[Blndbrm727]

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

[Kurt_Bremser]

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    

---

[Blndbrm727]

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;

[Kurt_Bremser]

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;

[Tom]

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.

[Tom]

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