BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Zeus_Olympous
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

Zeus_Olympous
Obsidian | Level 7

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.

Zeus_Olympous
Obsidian | Level 7

I think I found it ..I just added (drop=n:) 

 

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

 

Thank you very much
 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ballardw
Super User

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 939 views
  • 1 like
  • 3 in conversation