BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

I have a two datasets that I'm trying to join that have three variables that either have conflicting formats or values or both:

 

Dataset One 

FILL_DT: DATETIME18. (values look like 11JAN17:03:00:00)

RX_NBR: 12. (values look like 1038484)

NCPDP_ID: 15. (values look like 4542646)

 

Dataset Two

FILL_DT: MMDDYY10. (values look like 01/10/2017)

RX_NBR: $12. (values look like 000008829507) - notice this has leading zeros where as in the other dataset it doesnt, I want to get rid of these leading zeros. They don't all have the same number of leading zeros either.

NCPDP_ID: $15. (values look like 1482152)

 

So what I'd like is:

FILL_DT needs to be MMDDYY10. format in both datasets

RX_NBR needs to be 12. format in both datasets AND I need to get rid of leading zeros (from values from dataset 2)

NCPDP needs to be 15. in both datasets

 

I've tried using input and put but keep running into different errors like when I tried to convert from DATETIME to MMDDYY10. I get the message "Date value out of range". When I try to use input to convert from $15. to 15. the format becomes blank when I do a proc contents and when I try and join it tells me its of a different type.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

For fill_dt do the following:

format fill_dt mmddyy10.;
fill_dt = datepart(fill_dt);

For the variables in the second dataset, do

format intval 12.;
intval = input(rx_nbr,12.);
drop rx_nbr;
rename intval=rx_nbr;

Creation of a new variable has to be done because you can't change the type of an existing variable.

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

For fill_dt do the following:

format fill_dt mmddyy10.;
fill_dt = datepart(fill_dt);

For the variables in the second dataset, do

format intval 12.;
intval = input(rx_nbr,12.);
drop rx_nbr;
rename intval=rx_nbr;

Creation of a new variable has to be done because you can't change the type of an existing variable.

JediApprentice
Pyrite | Level 9

I see. Looks like that works. The only problem I have now is that RX_NBR in dataset 1 has values like 777955 but in dataset 2 is 000000777955. It's not always the case that there are 6 leading zeros also. Sometimes its just 5...They always have the same length but there coul be a value like 000007795555

Reeza
Super User

Is it a fixed length, ie always 12 chars? Is it char or numeric?

 

The numeric format fo add leading zeros is ZW - would be Z10. where the assumption is the number is 10 digits long. 

JediApprentice
Pyrite | Level 9

@Kurt_Bremser Actually that did get rid of the leading zeros, nice. I realized I accidentally dropped the temporary variable instead.

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
  • 4 replies
  • 1054 views
  • 2 likes
  • 3 in conversation