BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Melk
Lapis Lazuli | Level 10

I want to append all the datasets I have in my WORK library together. They all have the same column names, but they start on row 6. I have too many datasets to delete first 5 rows with a macro. Is there a good shortcut I can use for this, similar to the code I use to append them to 1 dataset:

 


PROC SQL;
    SELECT distinct MEMNAME INTO : MEMNAMES SEPARATED BY ' ' from dictionary.tables where libname='WORK';
quit;

DATA full;
    SET &MEMNAMES.;
RUN;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

include the translate function to convert the spaces to underscores. e.g.:

data test1;
  input (col1-col3) (&$);
  cards;
kjkj  jkjk  ljknl
kjcio  doik  roukg
kjor  ytiukk  lkjf
ruie  eooik  piijmf
item 1  item 2  item 3
1  2  3
4  5  6
;

data test2;
  input (col1-col3) (&$);
  cards;
kjkj  jkjk  ljknl
kjcio  doik  roukg
kjor  ytiukk  lkjf
ruie  eooik  piijmf
item 1  item 2  item 3
7  8  9
2  2  2
;

data _null_;
  array namelist $32. col1-col3;
  set test1(firstobs=5 obs=5);
  length names $32000;
  do over namelist;
    names=catx(' ',names,vname(namelist),'=',
     translate(strip(vvalue(namelist)),'_',' '));
  end;
  call symput('renames',names);
run;

%put &renames.;

proc sql;
  create table dslist as
    select memname from dictionary.tables
      where libname='WORK'
  ;
quit;

data _null_;
  call execute ('data want; set ') ;
  do until (done);
    set dslist end=done;
    call execute (memname);
    call execute("(firstobs=6 rename=(&renames.))");
  end;
  call execute ('; run;') ;
  stop;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post

11 REPLIES 11
Astounding
PROC Star

Sure.  Let's start with something similar to what you used before:

 

proc sql;

create table dslist as

select memname from dictionary.tables

where libname='WORK';

quit;

 

Instead of creating a macro variable, this creates a data set holding the names of your data sets.  Then construct and execute the rest of the program using:

 

data _null_;

call execute ('data want; set ') ;

do until (done);

   set dslist end=done;

   call execute (memname);

   call execute('(firstobs=6)');

end;

call execute ('; run;') ;

stop;

run;

Melk
Lapis Lazuli | Level 10

Thank you - almost got it. It is selecting the first row as the variable names. Can I edit this to select row 5 as variable names (firstobs is still at 6)?

art297
Opal | Level 21

If all of your files have the same variables, you could just modify @Astounding's code as follows:

data test1;
  input (col1-col3) ($);
  cards;
kjkj jkjk ljknl
kjcio doik roukg
kjor ytiukk lkjf
ruie eooik piijmf
item1 item2 item3
1 2 3
4 5 6
;

data test2;
  input (col1-col3) ($);
  cards;
kjkj jkjk ljknl
kjcio doik roukg
kjor ytiukk lkjf
ruie eooik piijmf
item1 item2 item3
7 8 9
2 2 2
;

data _null_;
  array namelist $32. col1-col3;
  set test1(firstobs=5 obs=5);
  length names $32000;
  do over namelist;
    names=catx(' ',names,vname(namelist),'=',vvalue(namelist));
  end;
  call symput('renames',names);
run;

proc sql;
  create table dslist as
    select memname from dictionary.tables
      where libname='WORK'
  ;
quit;

data _null_;
  call execute ('data want; set ') ;
  do until (done);
    set dslist end=done;
    call execute (memname);
    call execute("(firstobs=6 rename=(&renames.))");
  end;
  call execute ('; run;') ;
  stop;
run;

Of course, that would have to be expanded if the problem is more complex.

 

Art, CEO, AnalystFinder.com

 

Melk
Lapis Lazuli | Level 10
Hello - I am a little unclear for the array - I need to input the names of the current column names?
art297
Opal | Level 21

Yes! Instead of:

  array namelist $32. col1-col3;

col1-col3 should be replaced with your actual variable names

 

Art, CEO, AnalystFinder.com

 

Melk
Lapis Lazuli | Level 10
getting an error message at the second data _null_. Can you help me troubleshoot? THank you!

ERROR 214-322: Variable name = is not valid.

ERROR 23-7: Invalid value for the RENAME option.


***error was given per log at the 2nd column name rename

ERROR 79-322: Expecting a =.
Melk
Lapis Lazuli | Level 10

Ah, it is because the actual variable names in line 5 have spaces...

art297
Opal | Level 21

include the translate function to convert the spaces to underscores. e.g.:

data test1;
  input (col1-col3) (&$);
  cards;
kjkj  jkjk  ljknl
kjcio  doik  roukg
kjor  ytiukk  lkjf
ruie  eooik  piijmf
item 1  item 2  item 3
1  2  3
4  5  6
;

data test2;
  input (col1-col3) (&$);
  cards;
kjkj  jkjk  ljknl
kjcio  doik  roukg
kjor  ytiukk  lkjf
ruie  eooik  piijmf
item 1  item 2  item 3
7  8  9
2  2  2
;

data _null_;
  array namelist $32. col1-col3;
  set test1(firstobs=5 obs=5);
  length names $32000;
  do over namelist;
    names=catx(' ',names,vname(namelist),'=',
     translate(strip(vvalue(namelist)),'_',' '));
  end;
  call symput('renames',names);
run;

%put &renames.;

proc sql;
  create table dslist as
    select memname from dictionary.tables
      where libname='WORK'
  ;
quit;

data _null_;
  call execute ('data want; set ') ;
  do until (done);
    set dslist end=done;
    call execute (memname);
    call execute("(firstobs=6 rename=(&renames.))");
  end;
  call execute ('; run;') ;
  stop;
run;

Art, CEO, AnalystFinder.com

 

Astounding
PROC Star

I can't see what you are looking at.  You will have to show me the first 6 observations from one of the data sets for me to participate in this.  (I wouldn't need all the variables, but 3 or 4 would be necessary.)

 

In general, it would not be a bad idea to do this as two separate steps ... combine all the data values as one step, and go back and rename variables as a second step.  As Art indicated, it can be automated but I can't see what you are looking at.

mkeintz
PROC Star

This is a question the justifies use of global options firstobs=, as in:

 

PROC SQL;
  SELECT distinct MEMNAME INTO : MEMNAMES SEPARATED BY ' ' from dictionary.tables where libname='WORK';
quit;

options firstobs=6;
DATA full;
    SET &MEMNAMES.;
RUN;
options firstobs=1;

 

The firstobs=6 option will be applied to every data set subsequently being read in.  After the successful append, reset option to its default (firstobs=1).

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Melk
Lapis Lazuli | Level 10

firstobs=6 is working but it is still labeling the columns based on some text in A1 and just default excel column letters B..C..D.... for the rest. firstobs=1 is not changing the column labels to variable names in row 5.

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
  • 11 replies
  • 1872 views
  • 5 likes
  • 4 in conversation