Convert all numeric to char with apply the format of each var

[Ronein]

Hello

In my data set have numeric variables and char variables.

Some of numeric variables have user defined format that applied to them.

Some of numeric variables have regular numeric format best8.

My question:

I want to convert all numeric variables to char variables using the following rule:

If  numeric var has user defined format then the conversion should be done with PUT(numeric_Vat, user_Fmt)

If  numeric var has regular numeric format (best8.) then  conversion should be done with PUT(numeric_Vat, best.)

Please note that there are different user define formats applied on different numeric vars

What is the way to do it automatically that sas will convert the char vars by the criteria I mentioned?

proc format;
value X_Fmt
1='A'
2='B'
;
value Y_Fmt
1='Y'
2='N'
;
Run;

data have;
format x X_Fmt.  y Y_Fmt.;
input ID  X Y W Z;
cards;
1 1 1 1 2
2 1 2 2 2
3 1 1 3 2
4 2 2 3 2
5 2 1 2 1
;
Run;


data want(Drop=X Y W Z rename=(X_=X  Y_=Y  W_=W  Z_=Z));
set have;
X_=put(X,x_fmt.);
Y_=put(Y,y_Fmt.);
W_=put(W,best.);
Z_=put(Z,best.);
Run;

[Tom]

Use the VVALUE() function to get the formatted value of a variable.

x_=vvalue(x);

Or just write the data to text file and read it back in.

fliename dummy temp;
data _null_;
  set sashelp.class;
  file dummy dsd ;
  put (_all_) (+0);
run;

data want;
  infile dummy dsd truncover;
  input name :$20. sex :$8. (age height weight) (:$12.) ;
run;

proc print;
run;

[Tom · Accepted solution]

You can do it with PROC EXPORT/PROC IMPORT, if you use the EFI_ALLCHARS macro variable to control how PROC IMPORT reads the file.

Example:

filename dummy temp;
proc export data=have file=dummy dbms=csv; run;
%let efi_allchars=YES;
proc import file=dummy dbms=csv out=want replace; 
  guessingrows=max;
run;
%symdel efi_allchars;

proc contents data=want varnum; run;
proc print data=want; run;

Here is the HAVE variable definitions:

Here is the resulting WANT dataset.

[Ronein]

Perfect solution and thank you.

This is perfect solution because it is dynamic (Work on any set of variables and user shouldn't type the variables names)

and also the lengths of the resulted variables are fitted to the values .

I have some questions please:

1-Export Step-

Where is the dummy file created?

Where can I see it?

Is it CSV file?

Why did you choose to export to CSV file?

Is it essential to use "Dummy" word in file statement or can choose any name?

filename dummy  temp;
proc export  data=have file=dummy  dsbms=csv;
Run;

2-Import process

%let efi_allchars=YES;

Does it mean that all varaibles will be defined has char during import process?

guessingrows=max

Does it mean that SAS use max number of observations in order to determine column length?

[Tom]

DUMMY is just the fileref value I picked. You can use any fileref you want (as long as it does not conflict with some other fileref you need). 

TEMP engine makes the file in the WORK directory so you don't have to worry about giving it a name or about deleting it.  If you want to keep it you could make up a permanent filename instead.

If you want to look at if it you can. Just use the fileref in another program, such as:

* dump first five lines to the log ;
data _null_;
  infile dummy obs=5;
  input;
  list;
run;

I used a CSV file because the EFI_ALLCHARS setting only applies to reading of delimited text file.  It does not matter whether you use comma as the delimiter or some other character.  Just be consistent.

The GUESSINGROWS=MAX will insure that it does not truncate the data.  (Note that I think PROC IMPORT does not count properly when the values contain the delimiters.  It counts the quotes that are added around the values that contain delimiters, which can cause it to make the variables slightly longer than they need to be.)

Example where PROC IMPORT makes the variable longer than it has to be:

Spoiler

Variable A only needs to be 3 bytes long.

filename dummy temp;
options parmcards=dummy;
parmcards;
id,a,b,c
Alice,"1,2",XX,2
Betty,,,3
Carol,4,5,
;

%let efi_allchars=YES;
proc import file=dummy dbms=csv out=want replace;
  guessingrows=max;
run;
%symdel efi_allchars;

proc contents data=want varnum;
run;

proc print;
run;

Also the EFI_ALLCHARS setting has a bug.  It can get fooled into making a column as numeric if any of the values happen to just be a period. That can only happen with a character variable since a missing numeric value would have been written as nothing at all. 

Example of '.' breaking EFI_ALLCHARS=YES setting.

Spoiler

filename dummy temp;
options parmcards=dummy;
parmcards;
id,a,b,c
Alice,.,XX,2
Betty,,.,3
Carol,4,5,
;

%let efi_allchars=YES;
proc import file=dummy dbms=csv out=want replace;
  guessingrows=max;
run;
%symdel efi_allchars;

proc contents data=want varnum;
run;

[Ronein]

In this example it works 100% but in real word there were some columns that the values are not thr formatted values in the wanted data set