topic Re: Conflicting Formats/Values in SAS Programming

Conflicting Formats/Values

JediApprentice — Tue, 17 Jan 2017 21:37:57 GMT

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.

Re: Conflicting Formats/Values

Kurt_Bremser — Tue, 17 Jan 2017 21:44:34 GMT

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.

Re: Conflicting Formats/Values

JediApprentice — Tue, 17 Jan 2017 21:57:12 GMT

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

Re: Conflicting Formats/Values

Reeza — Tue, 17 Jan 2017 21:58:25 GMT

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.

Re: Conflicting Formats/Values

JediApprentice — Tue, 17 Jan 2017 22:07:41 GMT

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