topic Re: how to remove all variable names from datasets in SAS Programming

how to remove all variable names from datasets

Cyndia — Fri, 13 Apr 2012 04:50:59 GMT

Hi,

I need to PROC APPEND a few hundred datasets, but the variable names in all datasets are different. is there a way to remove all variable names so they all become VAR1, VAR2, VAR3...VAR50?

dataset 1 looks like this:

name age ID

------------------------

Cindy 15 101

Kenny 10 102

...

dataset 2 looks like this:

firstname yrs Identification

------------------------------------

Liz 13 103

Harry 17 104

...

there is a problem with PROC APPEND when variables are named differently. is there a way to remove all variable names from all datasets, so all variables become Var1, Var2...only?

Thanks!

Re: how to remove all variable labels from datasets

Cyndia — Fri, 13 Apr 2012 04:55:49 GMT

so the final appended dataset can become?

VAR1 VAR2 VAR3

-----------------------------------

Cindy 15 101

Kenny 10 102

...

Liz 13 103

Harry 17 104

...

i can label all variables after appending all datasets. any suggestions?

thanks.

Re: how to remove all variable labels from datasets

manojinpec — Fri, 13 Apr 2012 05:31:37 GMT

Hi, You can append the datasets and then can apply label.I have tried and i don't find any problem.

Can you elaborate what is the issue if we append with different labels.

Re: how to remove all variable labels from datasets

Ksharp — Fri, 13 Apr 2012 06:03:04 GMT

data class1;
  set sashelp.class;
  label age='AGE GROUP';
run;

data class2;
  set sashelp.class;
  label sex='Gender';
run;


data _null_;
 set sashelp.vmember(keep=memname) end=last;
 if _n_ eq 1 then call execute('proc datasets library=work nolist;');
 call execute('modify '||trim(memname)||'; attrib _all_ label=" ";');
 if last then call execute('quit;');
run;

Ksharp

Re: how to remove all variable labels from datasets

shivas — Fri, 13 Apr 2012 09:32:50 GMT

Hi Cyndia,

Try this code...Hope it helps.

data one;

input produce $ weight bags;

cards;

carrots 10 500

potatos 5 200

garlic 2 100

;

run;

data two;

input fruit $ wt containers;

cards;

apples 15 100

pears 9 20

;

run;

%macro test;

proc sql;

create table libtables as select memname from sashelp.vtable

where libname eq %upcase("work");

quit;

proc sql;

select count(*) into :i from libtables;

select memname into :j1- :j%left(&i) from libtables;

quit;

%do ii=1 %to %left(&i);

%global renlst&ii;

proc sql noprint;

select compress(name || '= v_'||compress(left(put(varnum,5.)))) into: renlst&ii separated by ' '

from dictionary.columns

where libname='WORK' and memname="&&j&ii.";

quit;

%end;

%mend test;

%test;

%put &renlst1 &renlst2 ;

data want;

set one(rename=(&renlst1)) two(rename=(&renlst2));

run;

Thanks,

Shiva

Re: how to remove all variable labels from datasets

Linlin — Fri, 13 Apr 2012 10:53:18 GMT

you don't have to have same labels to run proc append as long as variable names are the same.

Re: how to remove all variable labels from datasets

andreas_lds — Fri, 13 Apr 2012 12:58:38 GMT

Hi Cyndia,

it seems, that you are mixing up the terms "label" and "name".

Assuming, that the datasets are stored in the library "bob":

proc sql noprint;

create table work.UglyColumns as

select MemName as DatasetName, Name as Variable, VarNum as Position

from sashelp.vcolumn

where lowcase(LibName) = 'bob'

order by DatasetName, VarNum

;

quit;

data _null_;

set work.UglyColumns end=done;

by DatasetName;

if _n_ = 1 then do;

call execute('proc datasets library=bob nodetails nolist;');

end;

if first.DatasetName then do;

call execute('modify ' || DatasetName || ';');

call execute('rename ');

end;

call execute(catt(Variable, ' = Var', Position));

if last.DatasetName then do;

call execute(';'); /* rename-statement */

end;

if done then do;

call execute('run;');

end;

run;

Re: how to remove all variable labels from datasets

Cyndia — Fri, 13 Apr 2012 15:38:47 GMT

Sorry, I meant variable names are all different. andreas_lds is right about the mix-up.

Re: how to remove all variable labels from datasets

Cyndia — Fri, 13 Apr 2012 15:45:34 GMT

sorry...i meant variable names instead. i try to append all datasets that have different variable names. not labeling issue.

Re: how to remove all variable names from datasets

Peter_C — Fri, 13 Apr 2012 17:30:29 GMT

Use SQL. UNION ALL without corresponding

THen column order is used rather than name

Re: how to remove all variable names from datasets

Patrick — Sat, 14 Apr 2012 00:19:33 GMT

As Peter C suggests a SQL UNION ALL should do the trick IF your variables with different names but the same meaning are in ALL data sets in the same order.

As you're having hundreds of data sets consider generating the SQL code like done in code below.

/* create test data */
data ds1 ds10 ds5;
   input name $ age ID $;
   datalines;
Cindy     15     101
Kenny    10     102
;
run;
data ds2 ds7 ds4;
   input firstname $ yrs Identification $;
   datalines;
Liz               13     103
Harry           17     104
;
run;

/* DataSets: View containing all data sets for concatenation */
proc sql noprint;
   create view DataSets as
      select cats(libname,'.',memname) length=65 format=$65. as table
      from dictionary.tables
      where libname='WORK' and memname like 'DS%'
    ;
quit;

/* Variable mapping for output data set (="want") */
data mapping;
   attrib
      Name length=$20 label='Name'
      Age   length=8   label='Age'
      ID    length=$10 label='ID'
   ;
   call missing(of _all_);
   stop;
run;

/* create concatenation code and write it to temporary file */
filename mycode temp;
data _null_;
/*   file print;*/
   file mycode;
   set DataSets end=last;
   if _n_=1 then
   do;
      put
         @1 'proc sql;'
         /@3 'create table want as'
         /@6 'select * from mapping'
         ;
   end;
   put
      @6   'union all'
      /@6 'select * from ' table
      ;
   if last then
   do;
      put
         @3 ';'
        /@1 'quit;'
        ;
   end;
run;

/* include temporary file so that code gets executed */
%include mycode;

Re: how to remove all variable names from datasets

Cyndia — Mon, 16 Apr 2012 18:55:45 GMT

Yes, UNION ALL is a good method to concatenate multiple datasets that have the same attributes and the same order.

Re: how to remove all variable names from datasets

Cyndia — Mon, 16 Apr 2012 21:50:11 GMT

the datasets are already in a library. I tried to make the code to UNION ALL the datasets in my library, but encountered errors.

libname temp 'c:\test';

run;

/* create a list of the datasets */

filename indata pipe 'dir C:\all_datasets /b';

data work.list;

length fname $60;

infile indata truncover;

input fname $60.;

call symput ('num_files',_n_);

run;

/*using a DATA _NULL_ step, we can now read the

data set names in our database and save them in macro

variables */

data _null_;

set work.list;

call

symput('DataSet'||left(_n_),memname);

run;

/* DataSets: View containing all data sets for concatenation */

proc sql noprint;

create view DataSets as

select cats(libname,'.',memname) length=65 format=$65. as table

from dictionary.tables

where libname='temp' and memname like _& /* my dataset names are like this _384_abc_* not sure of this line */

;

quit;

/* Variable mapping for output data set (="want") */

data mapping;

attrib

ID_number length=$15 label='ID Number'

First length=$25 label='First Name'

...

;

call missing(of _all_);

stop;

run;

/* create concatenation code and write it to temporary file */

filename alldata temp;

data _null_;

/* file print;*/

file alldata;

set work.list end=last;

if _n_=1 then

do;

put

@1 'proc sql;'

/@3 'create table want as'

/@6 'select * from mapping'

;

end;

put

@6 'union all'

/@6 'select * from ' table /* got message "variable table is uninitialized, why? */

;

if last then

do;

put

@3 ';'

/@1 'quit;'

;

end;

run;

/* include temporary file so that code gets executed */

%include alldata;

Re: how to remove all variable names from datasets

Patrick — Tue, 17 Apr 2012 12:29:05 GMT

"where libname='temp' and memname like _& /* my dataset names are like this _384_abc_* not sure of this line */"

Very often values in dictionary tables are in uppercase. This is also true for "libname" and "memname".

If I understand your dataset naming pattern correct then all table names start with "_384_abc_" followed by 0 or more other characters (eg: _384_abc_somthing).

In SQL the character '%' is a wildcard for n characters, '_' is a wildcard for exactly one character. But as you have actually underscores in your name as a character, we can't use '_' as this would stand for any character not only an underscore.

One can mask these special characters (escape '<escape character>).

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473693.htm

That's how I believe your line of code should look like in order for things to work:

where libname ='TEMP' and memname like '\_384\_ABC\_%' escape '\'

Re: how to remove all variable names from datasets

Peter_C — Thu, 19 Apr 2012 21:23:50 GMT

Patrick is right these data sets will have uppercase names. The physical names are normally lower case. So why would you want read the physical directory when the sashelp.vmember view would provide an already prepared list ?

I don't know if anyone has checked but I think there is a limit to the number of tables read in an SQL statement! SELECT use to be limited to 16 and that was doubled when SAS8 came in. I expect it may have more than doubled since then. However, it might not perform for "100s" of tables.

Good luck.

Peter