topic Re: Numeric column with character data. PUT/Input not working in SAS Programming

Numeric column with character data. PUT/Input not working

Shri04 — Tue, 25 Apr 2023 12:13:56 GMT

Hello,

I am trying to format a column which is defined as numeric but have character data. Details are as below:

Length 3.

Format FCLMS6A9.

Informat 9.

Data Values:

No Claims

A00

0A0

00A

200

011

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.

Re: Numeric column with character data. PUT/Input not working

AMSAS — Tue, 25 Apr 2023 12:36:55 GMT

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 ;

Re: Numeric column with character data. PUT/Input not working

PaigeMiller — Tue, 25 Apr 2023 13:09:27 GMT

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.

Re: Numeric column with character data. PUT/Input not working

juansalasa — Tue, 25 Apr 2023 12:59:54 GMT

hello please send completed code you have to help you

Re: Numeric column with character data. PUT/Input not working

Tom — Tue, 25 Apr 2023 13:18:52 GMT

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;