topic Re: Alphabetic prefixes for enumerated data sets are different in SAS Programming

Alphabetic prefixes for enumerated data sets are different

Babloo — Fri, 09 Feb 2018 06:09:04 GMT

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.

Re: Alphabetic prefixes for enumerated data sets are different

andreas_lds — Fri, 09 Feb 2018 06:26:15 GMT

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.

Re: Alphabetic prefixes for enumerated data sets are different

Babloo — Fri, 09 Feb 2018 06:49:26 GMT

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_:;

Re: Alphabetic prefixes for enumerated data sets are different

SASKiwi — Fri, 09 Feb 2018 08:00:11 GMT

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.

Re: Alphabetic prefixes for enumerated data sets are different

Babloo — Fri, 09 Feb 2018 08:12:20 GMT

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?

Re: Alphabetic prefixes for enumerated data sets are different

SASKiwi — Fri, 09 Feb 2018 08:24:27 GMT

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;

Re: Alphabetic prefixes for enumerated data sets are different

ShiroAmada — Fri, 09 Feb 2018 08:51:27 GMT

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.

Re: Alphabetic prefixes for enumerated data sets are different

Astounding — Fri, 09 Feb 2018 12:41:08 GMT

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'

Re: Alphabetic prefixes for enumerated data sets are different

Babloo — Fri, 09 Feb 2018 12:28:49 GMT

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.

Re: Alphabetic prefixes for enumerated data sets are different

Babloo — Fri, 09 Feb 2018 12:38:32 GMT

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

Re: Alphabetic prefixes for enumerated data sets are different

Astounding — Fri, 09 Feb 2018 12:44:34 GMT

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.

Re: Alphabetic prefixes for enumerated data sets are different

Babloo — Fri, 09 Feb 2018 12:52:08 GMT

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?

Re: Alphabetic prefixes for enumerated data sets are different

Astounding — Fri, 09 Feb 2018 12:54:44 GMT

select distinct('libname.' || memname)

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

Re: Alphabetic prefixes for enumerated data sets are different

Tom — Fri, 09 Feb 2018 16:13:00 GMT

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

Re: Alphabetic prefixes for enumerated data sets are different

Reeza — Fri, 09 Feb 2018 16:36:41 GMT

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?