DATA Step, Macro, Functions and more

Alphabetic prefixes for enumerated data sets are different

Reply
PROC Star
Posts: 634

Alphabetic prefixes for enumerated data sets are different

 

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.

 

Valued Guide
Posts: 631

Re: Alphabetic prefixes for enumerated data sets are different

[ Edited ]

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.

PROC Star
Posts: 634

Re: Alphabetic prefixes for enumerated data sets are different

Posted in reply to andreas_lds

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

 

Super User
Posts: 4,034

Re: Alphabetic prefixes for enumerated data sets are different

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. 

PROC Star
Posts: 634

Re: Alphabetic prefixes for enumerated data sets are different

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?

Super User
Posts: 4,034

Re: Alphabetic prefixes for enumerated data sets are different

[ Edited ]

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;
Super User
Posts: 6,939

Re: Alphabetic prefixes for enumerated data sets are different

[ Edited ]

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'

PROC Star
Posts: 634

Re: Alphabetic prefixes for enumerated data sets are different

Posted in reply to Astounding

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.

Super User
Posts: 6,939

Re: Alphabetic prefixes for enumerated data sets are different

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.

PROC Star
Posts: 634

Re: Alphabetic prefixes for enumerated data sets are different

Posted in reply to Astounding

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?

Super User
Posts: 6,939

Re: Alphabetic prefixes for enumerated data sets are different

select distinct('libname.' || memname)

 

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

Super User
Posts: 24,028

Re: Alphabetic prefixes for enumerated data sets are different

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?


 

Super User
Super User
Posts: 8,290

Re: Alphabetic prefixes for enumerated data sets are different


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 ;
  ....
PROC Star
Posts: 634

Re: Alphabetic prefixes for enumerated data sets are different

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

Frequent Contributor
Posts: 118

Re: Alphabetic prefixes for enumerated data sets are different

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.

Ask a Question
Discussion stats
  • 14 replies
  • 519 views
  • 6 likes
  • 7 in conversation