BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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:

Tom_1-1684082878212.png

Here is the resulting WANT dataset.

Tom_0-1684082812944.png

 

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

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
Super User Tom
Super User

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:

Tom_1-1684082878212.png

Here is the resulting WANT dataset.

Tom_0-1684082812944.png

 

Ronein
Meteorite | Level 14

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
Super User Tom
Super User

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;
Tom_0-1684104055884.png

 



 

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
Meteorite | Level 14
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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 708 views
  • 7 likes
  • 2 in conversation