I am trying to format a column which is defined as numeric but have character data. Details are as below:
I want to format as :
if 'No Claims' then 0;
if '00A' then 1; if '011' then 2 etc
I tried PUT function, user defined format but nothing is working. Every time either SAS returns missing values or by default it converts
'No Claims' to 1,
'A00' to 7,
'00A' to 5, '011' to 8, 200 to 13 etc.
Is there any way out?
Thanks in advance.
I recommend you look at the documentation for PROC FORMAT
Here's a simple example using the PROC FORMAT CNTLIN data to create a custom format
/* Create a PROC FORMAT cntlin dataset and a sample "have" dataset */ data work.cntlin work.have (keep=start) ; retain fmtName "$myCustomFormat" label 0 ; infile cards ; input start $10. ; output work.cntlin ; label+1 ; output work.cntlin work.have ; cards ; No Claims A00 0A0 5+ 3 4 00A 200 011 ; run ; /* Create character myCustomFormat using cntlin data */ proc format cntlin=work.cntlin ; run ; /* test myCustomFormat against sample "have" data */ data want ; set work.have ; want=putc(start,"$myCustomFormat.") ; put start= want= ; run ;
Essentially, the format FCLMS6A9. makes the numeric value appear as character. But the values are still numeric. That's what formats do, they change the appearance of data, without changing the value.
I think to provide further help, we would need to see the PROC FORMAT code that creates format FCLMS6A.
A numeric variable cannot contain strings like A00. So if the variable is numeric and when you print the values you see strings like A00 then that is the result of applying the format to the numeric values.
So if you want to see the actual numbers then just display the variable without the format attached.
So if your existing dataset is named HAVE and existing variable is named CLAIMS try running this code to see what the values actually look like.
data want; set have; new_number=claims; run; proc freq data=want; tables new_number*claims / list missing; run;
You should see an output that shows that 1 is mapped to 'No Claims' and 7 is mapped to 'A00' etc.
So if you want to recode the numbers you could just write logic to do it.
data want; set have; select (claims); when (1) then new_number=0; when (7) then new_number=1; ... other new_number=.; end; run;
If you want to use formats or informats to do the re-mapping then you will need to create an INFORMAT that can map the values displayed by your existing FORMAT to the numbers you want.
proc format; invalue recode 'No Claims'=0 'A00'=1 .... ; run; data want; set have; new_number = input(vvalue(CLAIM),recode.); run;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.