Here is a solutions:   data have; input id quarter1 quarter2 quarter3; cards; 1            1                  0                    0 1            1                  0                    0 1            0                  1                    0 2            0                  0                    1 2            0                  0                    1 2            0                  0                    1 ; proc sql; create table want as select distinct id, sum(quarter1) as quarter1, sum(quarter2) as quarter2, sum(quarter3) as quarter3 from have group by id; ... View more

Here's a rough version, for the final I would add some macros so you don't have to know how many cities their are, macro variables should be able to take care of that and shorten the code:   data have; infile cards dsd dlm='*'; informat group $1. cities $100.; input group$ cities$; cards; A*Houston, Houston, New York, Los Angeles, Los Angeles B*Chicago, Boston, Boston C*Miami, Worcester, Worcester, Springfield, Springfield, Atlanta, Atlanta D*Oakland, Oakland, Oakland, Dallas, Dallas E*Lancaster, Boston, Madison, Madison, Madison, Madison ; data start; set have; comma=countc(cities,',')+1; scan1=strip(scan(cities,comma-0,',')); scan2=strip(scan(cities,comma-1,',')); scan3=strip(scan(cities,comma-2,',')); scan4=strip(scan(cities,comma-3,',')); scan5=strip(scan(cities,comma-4,',')); scan6=strip(scan(cities,comma-5,',')); scan7=strip(scan(cities,comma-6,',')); run; proc transpose data=start out=tran(drop=_NAME_);by group;var scan:; proc sort data=tran nodup;by group col1;where not missing(col1); proc transpose data=tran out=tran2(drop=_NAME_);by group;var col1; data want(keep=group unique); set tran2; unique=catx(',',of col1-col4); run; ... View more

Here is another solution:   data have; infile cards dsd; input category$ question$ response1$ response2$ response3$; cards; category,question,one,two,three ; run; %macro response; %do i = 1 %to 3; data response_&i; set have(keep=category question response&i); run; %end; %mend; %response; ... View more

The way you have it set up isn't logically correct.  Here it is broken down into two steps.  Hope this helps:   DATA TABLE_2010;    INPUT ID VAL;    DATALINES; 1  63 2  22 3  71 ;   DATA TABLE_2011;    INPUT ID VAL;    DATALINES; 4  68 5  55 6  54 ;   %macro first; %do i = 2010 %to 2011; data table_&i; set table_&i; other_val = "test_&i"; run; %end; %mend; %first; data final; %macro second; set %do i = 2010 %to 2011;  table_&i %end; %mend; %second; ; run;   I would recommend writting this out long hand and seeing what you can do without macros first.    Mark ... View more

Use underscore &i, so it will change the name for every iteration. ... View more

This is specific to your example.  If this doesn't work with your real data you should provide a more specific example:   data have; infile cards dsd; informat line $25.; input line; cards; # This is line one, Continue line one, # This is line two, Continue line two, ; data want; set have; count + 1; if count = 3 then count = 1; l_line = lag(line); if count = 2 then do;     wanted = catx(' ',l_line,line); end; run; ... View more