BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

 

24         data all_yearly_views_from_2017;
25         set COMM_TRANS_DET_2017_V00-COMM_TRANS_DET_2025_V00;
ERROR: Alphabetic prefixes for enumerated data sets (COMM_TRANS_DET_2017_V00-COMM_TRANS_DET_2025_V00) are 
       different.
26         run;

Appreciate if someone of you guide me to resolve this error. Also I want to add one more portion in this code. If any of the the views between the year 2017 and 2025 is not available (e.g. COMM_TRANS_DET_2018_V00 , COMM_TRANS_DET_2019_V00) I want the code to be successful by adding 0 observations for the views which are not available.

 

14 REPLIES 14
andreas_lds
Jade | Level 19

The enumerated part has to be the at the end of the name, so you have to remove _v00 to get the thing to work.

You could change your set-statement to 

set comm_trans_det_:;

if you are 100% that every dataset and view beginning with "comm_trans_det_" should be appended to "all_yearly_views_from_2017".

 

EDIT: if you change the set statement, you can keep _v00 at the end of the names.

Babloo
Rhodochrosite | Level 12

 I don't want to remove _v00 in the view name. If I add the following statement, it will append all the views, but I want to append the views only from 2017. Any idea on the second part of my question?

 

set comm_trans_det_:;

 

SASKiwi
PROC Star

Easy option - rename your datasets to comm_trans_det_V00_yyyy (eg 2017).

 

Harder option - use dictionary tables in an SQL query to create the exact list of datasets you want and assign the list to a macro variable which is then used on your SET statement instead of actual dataset names. There are plenty of examples in other posts of this technique. 

Babloo
Rhodochrosite | Level 12

I find hard to find the document/posts which you're talking about. Could you please point me with the link for the document which you wanted to mention?

SASKiwi
PROC Star

Untested:

proc sql noprint;
  select distinct memname
  into :table_list separated by ' '
  from dictionary.tables 
where memname contains '2017'; quit; data want; set &table_list; run;
Astounding
PROC Star

I think you're on to something here.  Perhaps the requirements call for something more like:

 

where upcase(memname) >= 'COMM_TRANS_DET_2017'

  and upcase(memname) < 'COMM_TRANS_DET_2026'

  and upcase(memname) contains '_V00'

Babloo
Rhodochrosite | Level 12

Thank you, code seem to be working fine. Is there a way to tweak the code in other way? Because I see that the code seem to take more time (due to querying dictionary.tables?) for completion.

Astounding
PROC Star

Notice a slight change to the code I posted (< 2026 instead of <= 2025).

 

To speed it up you can test whether it would be faster to exract from sashelp.vtable instead of dictionary.tables.  That's about all I can think of.  One is a view, the other a data set ... the data set should be faster but I forget which is which.

Babloo
Rhodochrosite | Level 12

sure, I will give a try. How to tweak your code if I've to append the permanent datasets which are available in non-work library?

Astounding
PROC Star

select distinct('libname.' || memname)

 

You have to replace libname with the actual libname that you are using.

Reeza
Super User

You can tweak it by using PROC DATASETS to generate the list of data sets instead of using SASHELP.VTABLE. If you have a lot of libraries and data sets querying SASHELP or DICTIONARY will take a while.

 


@Babloo wrote:

sure, I will give a try. How to tweak your code if I've to append the permanent datasets which are available in non-work library?


 

Tom
Super User Tom
Super User

@Babloo wrote:

Thank you, code seem to be working fine. Is there a way to tweak the code in other way? Because I see that the code seem to take more time (due to querying dictionary.tables?) for completion.


Make sure to include LIBNAME in your WHERE clause to prevent SAS from having to scan every libname to check what members it has.

proc sql noprint;
select distinct memname
  into :table_list separated by ' '
  from dictionary.tables
  where libname = 'WORK'
    and memname contains '2017'
;
quit;

Or just generate a macro variable with the names.

data _null_;
  length dslist $1000;
  do yr=2017 to 2025;
   dslist=catx(' ',dslist,catx('_','COMM_TRANS_DET',yr,'V00'));
  end;
  call symputx('dslist',dslist);
run;

data want;
  set &dslist ;
  ....
Babloo
Rhodochrosite | Level 12

How to tweak your code if I've to append the permanent datasets which are available in non-work library?

ShiroAmada
Lapis Lazuli | Level 10

Try this...

 

%macro Build_Tables;
%do yr=2017 %to 2025;
  data COMM_TRANS_DET_&&yr._V00;
    year=&&yr.;
  run;
%end;

data ALL_TABLES;
  set 
    %do yr=2017 %to 2025;
       COMM_TRANS_DET_&&yr._V00
    %end; ;
run;
%mend;
%Build_Tables;

Hope it works.

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
  • 14 replies
  • 6084 views
  • 6 likes
  • 7 in conversation