DATA Step, Macro, Functions and more

Conflicting Formats/Values

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

Conflicting Formats/Values

[ Edited ]

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.

 


Accepted Solutions
Solution
‎01-17-2017 05:07 PM
Super User
Posts: 7,779

Re: Conflicting Formats/Values

Posted in reply to JediApprentice

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎01-17-2017 05:07 PM
Super User
Posts: 7,779

Re: Conflicting Formats/Values

Posted in reply to JediApprentice

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 123

Re: Conflicting Formats/Values

[ Edited ]
Posted in reply to KurtBremser

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

Super User
Posts: 19,789

Re: Conflicting Formats/Values

Posted in reply to JediApprentice

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. 

Frequent Contributor
Posts: 123

Re: Conflicting Formats/Values

Posted in reply to KurtBremser

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 138 views
  • 2 likes
  • 3 in conversation