DATA Step, Macro, Functions and more

Convert Numeric to Character variables across datasets in a specified library

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Convert Numeric to Character variables across datasets in a specified library

Dear all,

 

I have a library e.g WORK with several tables tbl1, tbl2, tbl3....tbln

 

I also have certain variables say Var4, Var7,  etc. (I do not know how many at the onset) that they can have both types (Numeric and Character) across tables.

 

E.g. Var4 might be of numeric type in tbl1, tbl6 and of type Character in the rest  (n-2) tables.

        Var7 might be of numeric type in tbl9, tbl14,tbl21 and of type Character in the rest  (n-3) tables

etc.

 

I would like to come up with a macro that will loop through datasets of this specific library (e.g. WORK)

 

-  identify variables common in all tables that can be both numeric and character

-  convert the numeric type ones to character type so I will homogenise the double type variables into Character type.

 

Any help/suggestion will be greatly appreciated..

 

Thanking you in advance..

 

Best regards


Accepted Solutions
Solution
‎06-08-2017 05:55 AM
Super User
Super User
Posts: 7,401

Re: Convert Numeric to Character variables across datasets in a specified library

So, you can use sashelp.vtable and sashelp.vcolumn to ascertain metadata items.  

Here is an example, note I am assuming best is ok as format, length will not exceed 200, and columns names have enough characters to add n to them as the variables needs to be renamed and new ones created:

data a;
  a="1234";
  b=34;
run;

data b;
  a=12;
  b="45";
run;

proc sort data=sashelp.vcolumn out=cols;
  where libname="WORK" and memname in ("A","B");
  by name;
run; 

data cols;
  set cols;
  by name;
  if first.name and last.name then delete;
  if type="num" then output;
run;

proc sort data=cols;
  by memname;
run;

/* To to rename variables */
data _null_;
  set cols;
  by memname;
  if first.memname then call execute('data '||strip(memname)||'; set '||strip(memname)||' (rename=(');
  call execute(catx('=',name,"n"||strip(name)));
  if last.memname then call execute(')); run;');
run;

/* Assign new variables */
data _null_;
  set cols;
  by memname;
  if first.memname then call execute('data '||strip(memname)||'; set '||strip(memname)||';');
  call execute('length '||strip(name)||' $200; '||strip(name)||'=strip(put(n'||strip(name)||',best.));');
  if last.memname then call execute('run;');
run;

 

View solution in original post


All Replies
Solution
‎06-08-2017 05:55 AM
Super User
Super User
Posts: 7,401

Re: Convert Numeric to Character variables across datasets in a specified library

So, you can use sashelp.vtable and sashelp.vcolumn to ascertain metadata items.  

Here is an example, note I am assuming best is ok as format, length will not exceed 200, and columns names have enough characters to add n to them as the variables needs to be renamed and new ones created:

data a;
  a="1234";
  b=34;
run;

data b;
  a=12;
  b="45";
run;

proc sort data=sashelp.vcolumn out=cols;
  where libname="WORK" and memname in ("A","B");
  by name;
run; 

data cols;
  set cols;
  by name;
  if first.name and last.name then delete;
  if type="num" then output;
run;

proc sort data=cols;
  by memname;
run;

/* To to rename variables */
data _null_;
  set cols;
  by memname;
  if first.memname then call execute('data '||strip(memname)||'; set '||strip(memname)||' (rename=(');
  call execute(catx('=',name,"n"||strip(name)));
  if last.memname then call execute(')); run;');
run;

/* Assign new variables */
data _null_;
  set cols;
  by memname;
  if first.memname then call execute('data '||strip(memname)||'; set '||strip(memname)||';');
  call execute('length '||strip(name)||' $200; '||strip(name)||'=strip(put(n'||strip(name)||',best.));');
  if last.memname then call execute('run;');
run;

 

Contributor
Posts: 40

Re: Convert Numeric to Character variables across datasets in a specified library

Dear RW9,

 

Thanks ..your solution works..though in the final datasets I get both variables ..the initial numeric as e.g. [ na ] and the converted into character [ a ]

 

I would like to drop the initial numeric variables [na ] etc.

 

I believe I can do that in the second execute but I can not figure out..

 

Thank you in advance.

Contributor
Posts: 40

Re: Convert Numeric to Character variables across datasets in a specified library

I think I found it ..I just added (drop=nSmiley Happy 

 

if first.memname then call execute('data '||strip(memname)||' (drop=nSmiley Happy ; set '||strip(memname)||';');

 

Thank you very much
 

Super User
Super User
Posts: 7,401

Re: Convert Numeric to Character variables across datasets in a specified library

Yes, thats right.  do note that will drop any variable with the first character n, so be careful.  It may be safer if you can't decide on a prefix, to just have one more copy of that data _null_; which creates a datastep for the drop.

Super User
Posts: 10,500

Re: Convert Numeric to Character variables across datasets in a specified library


Zeus_Olympous wrote:

Dear all,

 

I have a library e.g WORK with several tables tbl1, tbl2, tbl3....tbln

 

I also have certain variables say Var4, Var7,  etc. (I do not know how many at the onset) that they can have both types (Numeric and Character) across tables.

 

E.g. Var4 might be of numeric type in tbl1, tbl6 and of type Character in the rest  (n-2) tables.

        Var7 might be of numeric type in tbl9, tbl14,tbl21 and of type Character in the rest  (n-3) tables

etc.

 


This, especially if the data sets are suppposed to be of the same structure, is a symptom of over-reliance on Proc Import. Import has to guess the varaible type based on the content every time it is used. So variables change types, lengths and sometimes even names depending on what is provided. Poorly controlled data, such as in most spreadsheet software, aggravates this behavior.

 

Better would be to determine what actual content the data is supposed to be and use a process with more control to preven having to fix what should not have been broken.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 113 views
  • 1 like
  • 3 in conversation