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
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
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
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;
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;
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.
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.;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.