I'm unsubscribing from this one so I won't get any further updates.   However, what part of "No.  Supply your SAS log, please!" did you not understand?   Good luck with your issue, and I'm happy you're feeling better. ... View more

Your original requirements: My query is how append every 3 days like 1st day: Ex_28. 2nd day: Append. Ex_28 Ex_29 3rd day: Append Ex_28 Ex_29 Ex_30 4th day: Append Ex_29 Ex_30 Ex_31 On 4th day we have to avoid first day data set   The solution:   data _null_; length ds_string $100; do date = today() - 2 to today(); ds_string = catx(' ',ds_string,'ex_' !! put(date,yymmddn8.)); end; call symputx('ds_string',ds_string); run; data want; set &ds_string.; run; Thanq it's working   How is this "working"?  Your requirements were to return one dataset on day 1, two datasets on day 2, 3 datasets on day 3, then a rolling 3 datasets backwards from day 4 onward.  The solution doesn't do that.  And you say that you only append every three days, not every day per the solution.   You need to learn to ask a better question if you expect a better answer (and in less than two pages and 13 posts).     ... View more

@thanikondharish wrote: My query is how append every 3 days like 1st day: Ex_28. 2nd day: Append. Ex_28 Ex_29 3rd day: Append Ex_28 Ex_29 Ex_30 4th day: Append Ex_29 Ex_30 Ex_31 On 4th day we have to avoid first day data set   You say append.  Do you mean concatenate instead?  If you're appending, wouldn't you just append to the base table each day?  Or are you wanting a rolling three days?   I started with this, but I'm bailing out.  I don't know exactly what you want to do.   * create some dummy datasets ; data dates; do date="01JUL2019"d to "31JUL2019"d; output; end; format date yymmddn8.; run; proc sql noprint; select date into :dates separated by " " from dates; quit; %macro code; data ex_&word; chr_date="&word"; * dummy data ; run; %mend; %loop(&dates) * initialize audit log ; data auditlog; length rundate startdate enddate 8; format rundate date9. startdate enddate yymmddn.; rundate = today(); startdate = today(); enddate = today(); run; proc sql; select intnx('day',max_enddate,1) as startdate format=yymmddn. ,intnx('day',max_enddate,3) as startdate format=yymmddn. into :startdate,:enddate from ( select max(enddate) as max_enddate from auditlog ) ; quit; %put &=startdate &=enddate; * loop over the dates, append the source tables, and write a row to the audit log. ; You don't explain what logic determines 1st day, 2nd day, 3rd day.   Maybe you can run with this code and make something work.   At this point I'm guessing at the details of what you want to do, and why you want to do it.   Perhaps get a colleague to help you better ask your question?   See https://github.com/scottbass/SAS/blob/master/Macro/loop.sas for the %loop macro. ... View more

Is there any way you can rename your datasets from this poorly designed naming convention to ex_yyyymmdd, for example ex_20190730, ex_20190731, ex_20190801, ...., ex_20200101, etc.?  It will make your life much easier. ... View more

@ChrisNZ wrote: Which would make the SAS Communities more unfriendly to newbies. Which is probably not a direction to go in. True. The opposite risk is that knowledgeable people tire of spending too much time trying to get a proper question, and start bothering less.   At the moment, the onus is on highly skilled experts to spend a lot of time on clerical and throw-away work rather than on adding the value they came here to add.   If that goodwill dries up because we don't push basic requirements on help seekers, the community is in much direr straights than anything we can do to "make the SAS Communities more unfriendly to newbies." This is not happening now (though this thread and others are a sign of impatience), but we should be careful about taking people for granted just because they haven't complained so far. Especially the most valuable people. Their motivation may drop, their interest may shift, their feeling of adding value may become too low.   Thanks for all the discussion.  And yeah @PaigeMiller is probably right.  I guess I want a solid, complete library article that I can bookmark, and when a noob posts a question, I can just paste that URL in my reply and move on.   It just seems there are a lot of noobs lately that don't know how to ask a question...I mean literally don't know how to ask a question in an online forum, let alone how to format one.  And as is obvious from my recent attempts at being the "post police", it's getting up my craw (https://www.dictionary.com/browse/stick-in-one-s-craw).   They will, with time and experience, learn how to ask a question.  I was there once, too many years ago.  But if they can at least make it easy for me to run their code, that would really be nice.   At the moment, the onus is on highly skilled experts to spend a lot of time on clerical and throw-away work rather than on adding the value they came here to add.   If that goodwill dries up because we don't push basic requirements on help seekers,   Yeah, I'm out.  I need to follow my own signature block.  If I can't paste their code into SAS and have it run without error, I'm off to the next question, even though  I actually like answering questions (when I have the time).  I like the challenge, their questions often help me learn as well, and can be fun - yeah I'm weird that way. ... View more

I was also playing with this, but naming your macro variables as you described is easier in a data step:   * for debugging, print to output window ; proc sql; * zone distinct count ; select count(0) as zone_count from (select distinct zone from test); * zone distinct values ; select distinct zone as zone_value from test; * distinct count of state within zone ; select zone, state, count(0) as zone_state_count from (select distinct zone, state from test) group by zone; * distinct value of state within zone ; select distinct zone, state as zone_state_value from test; * distinct count of branch within within zone + state ; select zone, state, branch, count(0) as zone_state_branch_count from (select distinct zone, state, branch from test) group by zone, state; * distinct value of branch within within zone + state ; select distinct zone, state, branch as zone_state_branch_value from test; quit; * I don't think you need macro variables, but if you REALLY want them ; proc sql; * zone distinct count ; select count(0) as zone_count into :zone_count trimmed from (select distinct zone from test); %put &=zone_count; * zone distinct values ; select distinct zone as zone_value into :zone1 - :zone9999 from test; %put &=zone1; %put &=zone2; * distinct count of state within zone ; * skipping this one... ; select zone, state, count(0) as zone_state_count from (select distinct zone, state from test) group by zone; * distinct value of state within zone ; select distinct zone, state as zone_state_value from test; * distinct count of branch within within zone + state ; select zone, state, branch, count(0) as zone_state_branch_count from (select distinct zone, state, branch from test) group by zone, state; * distinct value of branch within within zone + state ; select distinct zone, state, branch as zone_state_branch_value from test; * at this point I gave up... ; ... View more

q1)how to create macro variable for zone distinct count Ans:   2   Check   q2)how to create macro variable for distinct values of zone Ans:       zone1 OUTH                ZONE2:NORTH    Check   q3)how to create macro variable for distinct count of state with in zone Ans:      SOUTH_state_1:    AP              SOUTH_state_2:    TS                NORTH _state_1:    MH              NORTH _state_2:    GJ              NORTH _state_3     L   Unclear:  do you want count or values?   q3)how to create macro variable for distinct count of branch with in zone and state Ans:      SOUTH_AP_branch1:  VZG              SOUTH_AP_branch2:   TRP               SOUTH_AP_branch3:  GUN                SOUTH_state_TS_branch1:   HYD              SOUTH_state_TS_branch2:   WGL   Unclear:  do you want count or values?   My "gut" says this is BAD...explain why you want to create these macro variables?  I bet storing in a data set would be a better approach.   Anyway, here you go.  Adjust if I misunderstood your unclear criteria:   * reset from previous runs ; %global mvars; proc sql noprint; select name into :mvars separated by " " from dictionary.macros where name like 'ZONE%' or name like '%STATE%' or name like '%BRANCH%' order by name; quit; %symdel &mvars / nowarn; data have; input zone $ state$ branch$ sales; cards; SOUTH AP VZG 254 SOUTH AP TRP 247 SOUTH AP GUN 321 SOUTH TS HYD 147 SOUTH TS WGL 315 NORTH MH MUM 254 NORTH MH PUN 247 NORTH GJ AHM 323 NORTH DL NCR 148 NORTH DL NOD 319 ; run; * sort ; proc sort; by zone state branch; run; data _null_; set have end=eof; by zone state branch; if first.zone then do; zone_count+1; call symputx(catx("_","zone",zone_count),zone); end; if first.state then do; if first.zone then state_count=0; state_count+1; call symputx(catx("_",zone,"state",state_count),state); end; if first.branch then do; if first.zone or first.state then branch_count=0; branch_count+1; call symputx(catx("_",zone,state,"branch",branch_count),branch); end; if eof then call symputx("zone_count",zone_count); run; proc sql; select name, value from dictionary.macros where name like 'ZONE%' or name like '%STATE%' or name like '%BRANCH%' order by name; quit; ... View more

i want to save my sas program in folder with password protection. so if anyone one to open it they need password to read/write sas program   Why don't you just use operating system security?  Either Windows or *nix makes this trivial to configure.   so if anyone one to open it they need password   Does it matter when someone shares the password with others?   Another option is compiled macros.  Users could execute your macros, but would not be able to alter them.  With proper options within the macro they won't even be able to see the code in the log.  Hit the doc for details. ... View more