@Reeza How would I do a join on a table for a lookup? - I have a table with all of the values and corresponding codes in it. ... View more

Why not save yourself a bit of coding, and messing around with macro lists: proc sql; create table WANT as select * from HAVE where ID in (select ID from NEW_ID where DATE < '01JAN2011'd); quit; ... View more

I'd think that your log shows some other error before that. The following worked for me:   input id $ date $10.; cards; a 2013/03/01 b 2010/03/01 c 2010/04/02 ; data want; set have (rename=(date=_date)); format from_date date9.; from_date=input(_date,anydtdte10.); run; proc sql noprint; select ID into :ID_List_2010 separated by ' ' /* from EGTASK.'ID FROM DATES_UPDATED'n*/ from want where from_date < '01jan2011'd ; quit; Art, CEO, AnalystFinder.com ... View more

@Kurt_Bremser Actually that did get rid of the leading zeros, nice. I realized I accidentally dropped the temporary variable instead. ... View more

If the column's aren't coming out in desired sort order it is likely that is because the first ID does not have all of the List values. The order from the sort would cause the columns to be in order for the first ID value encountered and then a column added as new values of List are encounterd for other ID values. For example: data have; infile datalines; input list $ id price; datalines; DAC 22 2 DAC 11 3 DAC 33 1 BAC 11 4 BAC 33 2 BAC 22 5 CBA 33 5 CBA 22 4 CBA 11 7 AAA 41 3 ; run; Sorting by ID the List AAA doesn't occur until ID 41 so you get AAA as the LAST column.   You can prepend a dummy data set with a speciall value of ID to get the sort order correct and then drop from the final result. data have; infile datalines; input list $ id price; datalines; DAC 22 2 DAC 11 3 DAC 33 1 BAC 11 4 BAC 33 2 BAC 22 5 CBA 33 5 CBA 22 4 CBA 11 7 AAA 41 3 ; run; data dummy; Price=.; id = 01; /* should be BEFORE the first sort order ID actually in your data*/ length list $ 8; do LIST = 'DAC','BAC','AAA','CBA'; output; end; run; data temp; set dummy have; run; proc sort data=temp; by id list; run; proc transpose data=temp out=want(drop=_name_ where=(id ne 1)); by id; id list; var price; run; ... View more

Use the round() function to discard the small fractional values. These tiny values are artifacts of the way SAS stores and computes numerical values. ... View more

You can also use a left join and then filter the result to only pick-up records where the name from teenstudent2 is missing (=all records where there was no match to teenstudent2). Such an approach will return the same number of rows as long as the table relationship is not 1:M or M:M data work.teenstudents; set sashelp.class; where age > 12; run; data teenstudents2; set sashelp.class; where age > 14; run; proc sql; select a.* from teenstudents a left join teenstudents2 b on a.name=b.name having b.name is NULL ; quit; ... View more

You can't re-type a varaible from char to num or vice-versa, so you have to rename the vars, thereby freeing up the original varnames to use with the new var type.  Below shows how:   The SCAN functions is asking for the 2nd "word" in col2, where it defines word-separators as the character "M".   regards, Mark   data have1; infile datalines; format MailOrderIndicator $1. FilledMonth $7.; input MailOrderIndicator $ FilledMonth $; datalines; N 2016M09 Y 2016M09 ; run; data want; set have1 (rename=(mailorderindicator=col1 filledmonth=col2)); mailorderindicator=ifn(col1='Y',1,0); filledmonth=input(scan(col2,2,'M'),2.); run; ... View more

Apparently I didn't thought it through. So we are lucky that @PGStats came to the rescue  ... View more

@JediApprentice wrote: @Kurt_Bremser But SQL does take variables from both tables...  ONLY when they have different names. ... View more