That was my question too, if you have tried arrays why does it need to be in sql?  I'm curious to see another solution, but here is one.  Doesn't look that good to me but it gets the results: data t1; input id$; cards; a b c d ; data t2; infile cards missover; input id1$ val1  id2$ val2 id3$ val3; cards; a 200   b   10   a   20.22 b 300   c   30   c    2.12 c 400   d   20 d 100 ; proc sql; create table one as select a.id,b.val1 from t1 a left join      t2 b on a.id = b.id1; create table two as select a.*,b.val2 from one a left join      t2 b on a.id = b.id2; create table want as select a.*,b.val3 from two a left join      t2 b on a.id = b.id3; ... View more

I think this is what you are looking for: data have; infile cards missover; input year var1-var17; cards; 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 ; proc transpose data=have out=want (rename=(_NAME_ = Characteristic));id year; ... View more

I think this is what you want but what you have as your output doesn't match what this code will do.  If my output is wrong please explain why you have a blank for val2 in row 1 and 10 for val2 in row 2: proc sql; create table want as select a.*, b.val1, b.val2, b.val3 from t1 a left join      t2 b on a.id = b.id1; ... View more

data have; infile cards dsd; input header1$ header2$ header3 header4$ header5; cards; ad,grad,2015,O,25 ad,grad,2014,O,20 adms,home,2015,O,55 adms,home,2014,O,40 adms,not,2015,T,70 adms,not,2014,T,15 ; proc sort data=have;by header1 header2 descending header3; data prep; set have; diff_year = lag(header5); by header1 header2 descending header3 notsorted; if not first.header2 then do;     difference = diff_year-header5; end; run; proc sql; create table want as select distinct header1, header2, header3, header4, difference as header5 from prep having not missing(header5); ... View more

There are several ways to do this.  I have typed up almost the same exact solution as dcruik, you wouldn't want to us nodupkey though.  For this example you want nodup. data have; infile cards; input Name$ Number; cards; John              55 John              55 John              55 Ken               21 Ken               21 Ken               21 Ken               21 Steve            46 Steve            46 ; run; proc sql; create table want1 as select distinct * from have; proc sort data=have out=want2 nodup;by name number; ... View more

Without changing the syntax you should think of using a macro variable anytime you type something over again like this: %let list = ("4330","43301","43310","43311","43320","43321","43330","43331","43380","43381","43390","43391","4340","43401", "43410","43411","43490","43491","4350","4351","4352","4353","4358","4359"); proc sql; create table ABC as select t1.age,        t1.gender,        t1.diagnosis1,        t1.diagnosis2,        t1.diagnosis3,        t1.diagnosis4 from work.testfile t1     where t1.diagnosis1 in &list        or t1.diagnosis2 in &list        or t1.diagnosis3 in &list or t1.diagnosis_oth4_cd in &list; I would probably use a proc tranpose but I'm sure there are a lot of ways. ... View more

See if this helps: data have; infile cards; informat Customer_ID $20. PURCHASE_DATE date9. PRODUCT_CODE $6.; format Customer_ID $20. PURCHASE_DATE date9. PRODUCT_CODE $6.; input Customer_ID PURCHASE_DATE PRODUCT_CODE; cards; 4160012851159850 04DEC2014 PROD14 4160012851159850 18OCT2014 PROD17 416002285459214 18OCT2014 PROD17 416002285459214 11JAN2015 PROD18 416003285759484 20DEC2014 PROD13 416003285759484 11JAN2015 PROD14 4160042851059757 22FEB2015 PROD02 4160042851059757 18OCT2014 PROD26 4160042851059757 18MAY2015 PROD27 4160053041360036 18MAY2015 PROD02 4160053041360036 20OCT2014 PROD17 4160062851159850 04DEC2014 PROD04 4160062851159850 22FEB2015 PROD28 4160062851159850 03APR2014 PROD30 416007266559304 20OCT2014 PROD17 416007266559304 04DEC2014 PROD18 416008304959670 20OCT2014 PROD26 416008304959670 20OCT2014 PROD27 416009285859574 12SEP2014 PROD13 416009285859574 30APR2015 PROD14 416009285859574 03APR2014 PROD17 416010285859574 12SEP2014 PROD26 416010285859574 12SEP2014 PROD27 416011247659385 29JUN2014 PROD26 416011247659385 03APR2014 PROD27 4160123041360036 18MAY2015 PROD03 4160123041360036 20DEC2014 PROD28 4160123041360036 29JUN2014 PROD30 4160132661460123 30APR2015 PROD03 4160132661460123 29JUN2014 PROD28 416014285459214 18OCT2014 PROD26 416014285459214 18OCT2014 PROD27 416015304959670 20OCT2014 PROD25 416015304959670 12SEP2014 PROD28 416016304959670 20OCT2014 PROD06 416016304959670 03APR2014 PROD25 4160172851159850 04DEC2014 PROD25 4160172851159850 03APR2014 PROD26 4160302851159850 20OCT2014 PROD28 4160302851159850 18OCT2014 PROD30 416031266559304 20OCT2014 PROD28 416031266559304 03APR2014 PROD30 416032285459214 18OCT2014 PROD02 416032285459214 20OCT2014 PROD30 4160332851159850 04DEC2014 PROD03 4160332851159850 18OCT2014 PROD30 4160343041360036 18MAY2015 PROD03 4160343041360036 20DEC2014 PROD30 4160352661460123 30APR2015 PROD19 4160352661460123 03APR2014 PROD28 416036285459214 18OCT2014 PROD26 416036285459214 12SEP2014 PROD27 4160373041559124 03APR2014 PROD02 4160373041559124 20DEC2014 PROD13 416038285759484 20DEC2014 PROD13 416038285759484 03APR2014 PROD14 416038285759484 22FEB2015 PROD17 416039285459214 18OCT2014 PROD02 416039285459214 03APR2014 PROD17 416039285459214 11JAN2015 PROD18 4160403041559124 03APR2014 PROD13 4160403041559124 18OCT2014 PROD17 4160403041559124 11JAN2015 PROD19 4160412851059757 22FEB2015 PROD08 4160412851059757 18OCT2014 PROD28 4160422851159850 04DEC2014 PROD03 4160422851159850 03APR2014 PROD28 4160432851159850 04DEC2014 PROD03 4160432851159850 22FEB2015 PROD17 4160442851059757 22FEB2015 PROD26 4160442851059757 18OCT2014 PROD27 4160453041259946 11JAN2015 PROD19 4160453041259946 03APR2014 PROD28 4160463041360036 18MAY2015 PROD04 4160463041360036 03APR2014 PROD28 416047285459214 18OCT2014 PROD17 416047285459214 11JAN2015 PROD18 4160483041360036 18MAY2015 PROD04 4160483041360036 12SEP2014 PROD28 4160492851159850 04DEC2014 PROD03 4160492851159850 18OCT2014 PROD28 4160492851159850 03APR2014 PROD30 416050247659385 29JUN2014 PROD13 ; proc sort data=have;by Customer_ID product_code; proc transpose data=prep out=tran_prep (drop=_NAME_);by customer_id;var product_code; proc sql; create table want as select distinct catx('-',col1,col2,col3) as Products,count(calculated products) as count from tran_prep group by products order by count desc; ... View more