Hi @rkarr5
You can try this. It generate one dataset name according to the ordercode and containing 2 columns: year (with 1 observation per year from 2012 to 2018) and code.
I am not sure if this is what you want, as your entry data are very weird: lot of duplicate records and there is no rule to link codes "north" or "south" with Internet_1a.
/* List of tables to be created into macrovariables */
proc sql noprint;
select distinct ordercode into: ordercode_1-:ordercode_999 from table1;
select count(distinct ordercode) into: nb_ordercode from table1;
run;
/* Retrieve each couple of ordercode-code */
proc sort data=table1 out=table1_nodupkey (keep=ordercode code) nodupkey;
by ordertype ordercode code name;
run;
/* Output 1 row / year for each couple ordercode-code - Method 1*/
data table2;
set table1_nodupkey;
do year=2012 to 2018;
output;
end;
run;
%macro ordercode_tables();
%do i=1 %to &nb_ordercode;
data &&ordercode_&i (drop =ordercode);
set table2;
where ordercode = "&&ordercode_&i";
run;
*title "&&ordercode_&i";
* proc print data=&&ordercode_&i;
* run;
*title;
%end;
%mend;
%ordercode_tables;
... View more
