topic Re: transform all numeric varaibles into their formatted values in SAS Programming

transform all numeric varaibles into their formatted values

Ronein — Mon, 14 Jun 2021 14:34:59 GMT

Hello,

Let's say that I have a data set with many varaibles X1,X2.....X500.

Let's say that each varaible is numeric with format.

I want to transform each varaible (numeric) into char varaible that will get the formatted values.

I know to do it manually:

x1_char=put(x1,f1ff.);

X2_char=put(x2,agefmt.);

X3_char=put(X3, wealthFmt.);

and so on

The problem is that I have 500 varaibles and it is not a clever way to write this statement for each varaible.

My question:

What is the way to transform all numeric varaibles into their formatted values in a quick clever way?

Re: transform all numeric varaibles into their formatted values

jimbarbour — Mon, 14 Jun 2021 14:56:44 GMT

I would probably use a macro to generate the put statements. The macro would iterate through the dataset definition, obtain the varname and format, then %PUT the put statement. You would put the macro inline; in other words, it would be used within a Data step.

Jim

Re: transform all numeric varaibles into their formatted values

Ronein — Mon, 14 Jun 2021 15:03:12 GMT

May you show a code?
Thank you

Re: transform all numeric varaibles into their formatted values

jimbarbour — Mon, 14 Jun 2021 15:38:51 GMT

Yes, I could write something up, but actually as I think about it, I think it might be simpler to use VVALUE(). Give me a minute.

Jim

Re: transform all numeric varaibles into their formatted values

japelin — Mon, 14 Jun 2021 15:14:32 GMT

There are many ways to do this, but since the formatting information is stored in SASHELP.VCOLUMN, I think it is easier to store it in a macro variable and have it processed.

data have;
  format x1 z6.
         x2 date9.
         x3 dollar12.2
         x4 time5.
         x5 datetime16.;
  array x{5};
  do i=1 to 5;
    x{i}=10000;
  end;
  drop i;
run;
data _null_;
  set sashelp.vcolumn;
  where libname='WORK' and memname='HAVE';
  call symputx(cats('fmt',_n_),format);/* format to macro variable */
  call symputx(cats('name',_n_),name);/* variable name to macro variable */
  call symputx('nobs',_n_);/* loop count */
run;

%Macro Mtransform;
  data want;
    set have;
    length char1-char5 $200;
    %do i=1 %to &nobs;
      char&i=putn(&&name&i,"&&fmt&i");
    %end;
  run;
%Mend;
%Mtransform;

Re: transform all numeric varaibles into their formatted values

jimbarbour — Mon, 14 Jun 2021 15:38:02 GMT

Something like this:

DATA	Have;
	FORMAT	X1	DATE9.;
	FORMAT	X2	COMMA17.;
	FORMAT	X3	PERCENT8.2;

	INPUT	X1
			X2
			X3
			;

DATALINES;
22443  123456789  .9875
22444  999999999  .882
22445  100000000  .628
RUN;

PROC	CONTENTS	DATA=Have	
	OUT=Have_Columns	(KEEP=Name);
RUN;

DATA	_NULL_;
	SET	Have_Columns;
	CALL	SYMPUTX(CATS('VarName',PUT(_N_,8.)), Name, 'G');
	CALL	SYMPUTX('Nbr_of_Vars', PUT(_N_,8.), 'G');
RUN;

%MACRO	Create_Formatted_Char_Vars;
	%LOCAL	i;
	%DO	i	=	1	%TO	&Nbr_Of_Vars;
		%SYSFUNC(CATS(Char_, &&VarName&i)) = VVALUE(&&VarName&i);
	%END;
%MEND	Create_Formatted_Char_Vars;

DATA	Want;
	SET	Have;
	%Create_Formatted_Char_Vars;
RUN;

Which creates the below. Notice that the second three columns have the same values as the formatted first three columns but that the last three columns are character.

Jim

Re: transform all numeric varaibles into their formatted values

PaigeMiller — Mon, 14 Jun 2021 15:43:31 GMT

You have 500 numeric variables, and you want 500 character variables that are the numeric variables formatted. This seems redundant.

I would start by questioning the need for this. Because the simplest way to do this is to use the numeric variables, formatted, and not create the character variables. How simple is that?

What will you do with the character variables that you cannot do with formatted numeric variables?