How to re-write the below function into a macro. I need to run the below format for 7 months

dugan123 — Thu, 20 Jul 2017 09:24:31 GMT

proc sql;
create table step_1 as select *
from master
where date = '200101';
run;

proc sql;
create table step_2 as select *
from time_table
where date = 200102;
run;

proc sql;
create table step_3 as select a.*, b.*
from step_1 as a
left join step_2 as b
on a.date = b.time;
quit;

data step_5;
set step_4_1;
array Month (6) date_200101
date_200102

date_200103

date_200104

date_201510

date_201510

do i = 1 to 7;
if Month{i} in ('90','120','150','180') then do;
bad = 1;
mnth = i;
i = 7;
end;
else bad = 0;
end;

if month{7} in ('90','120','150','180') then do;
Bad_1 = 1;

end;

else bad_1 = 0 ;
count_1 = 1;
drop i;
run;

Re: How to re-write the below function into a macro. I need to run the below format for 7 months

RW9 — Thu, 20 Jul 2017 10:04:28 GMT

Well, lets back up here for a minute. First, it is quite hard to refactor anything without seeing a) some test data - in the form of a datastep, see this post if you need help:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

and b) required output.

A brief glance at your code provided shows a fair few areas for change, for instance just the first three steps could be:

proc sql;
  create table STEP_3 as 
  select  A.*,
          B.*
  from    (select * from MASTER where DATE='200101') A
  left join (select * from TIME_TABLE where DATE='200102') B
  on       A.DATE=B.TIME;
quit;

I assume date is a character string? If not then you need to put a date value in there - i.e. a numeric or date literal. this is where seeing actual data helps. Also, use of select * shows that you haven't thought about the data coming in (and can cause problems further on.

Thirdly, never start a problem by saying I need to use <xyz> technology, now how can I squeeze my problem into that as your sure to end up with a mess. Just from what you post there, it seems you have the "excel" way of working going on, i.e. data in column headings going across the page which isn't the best way to work. Put your data in the dataset, and name variables consistently and simple so that your code can access them without vast amounts of code needed to access the information. Also put date into the dataset as a proper date variable, this way you will be, by easy use of maths or date functions, be able to pull out any selection of data for processing, i.e. you can do 7 month windows on the data and then process using those groups.

So for me if I started with:

date_201401 date_201402...

123 567

First step would be to remodel the data for programming activities:

DATE RESULT

JAN2014 123

FEB2014 567

I thne know for my programming that there will always be two variables DATE and RESULT, and I can process dates using simple maths and date functions e.g.

DATE RESULT WINDOW

JAN2014 123 First 7 Months

FEB2014 567 First 7 Months

...

I can then group results, sum them etc. without loops and extra code, and if the output needs to look as it was, then a simple proc transpose call can achieve this, hence output as required, process data as required for programming - best of both worlds.

topic Re: How to re-write the below function into a macro. I need to run the below format for 7 months in SAS Programming

How to re-write the below function into a macro. I need to run the below format for 7 months

Re: How to re-write the below function into a macro. I need to run the below format for 7 months