where (visitdate gt "24617"d and visitmonth=0) and (cmvresultsaliva ne . or cmvresulturine Should be where (visitdate gt 24617 and visitmonth=0) and (cmvresultsaliva ne . or cmvresulturine ... View more

First back up and look at this line. "set dept=' ' where id in (select id from lookup) and sal in (select sal from lookup) " This looks to see if the ID is in the lookup, and then looks to see if the sal is in the lookup. It DOES NOT look to see if the ID and SAL are associated. SO id = 1, SAL = 300 Will match if 2 records exist such as ID = 1, SAL = 200 and ID = 3, SAL = 300 Just add a case block for each condition and assign, there is no need to go through the data more than once. ... View more

I would suggest you set options MPRINT; or put a %put =&tecvtdate; right before the where, to make sure that =&tecvtdate resolves to a number. The format is not important. ... View more

data new; set ; array orig {5} var1-var5; do i = 1 to 4; v1 = orig(i); v2 = orig(i+1); if v2 ne '' then output; end; run; proc sort nodupkey; by v1 v2; run; ... View more

I would think SQL would handle this. Proc sql; create table file3 as select case when dept = 'sales' and l.sal ne . then 's' when dept = 'purchase' and l.sal ne . then 'p' else a.id end as id, case when dept = 'sales' and l.sal ne . then 250 when dept = 'purchase' and l.sal ne . then 350 else a.sal end as sal, dept from file1 a left join lookup l on a.id = l.id and a.sal = l.sal; (untested) ... View more

"if I use call execute and call macro which has PROC SQL from a data step it will work." It will work, but the generated code is executed after the data step, not during it. The code is stored in a buffer and executed as if it was an included file. ... View more

look at vname and vnamex ... View more

Even if you fix this error your code will not work. You are trying to generate multiple "KEEP" statements. Only one may be used. Perhaps you are looking for an OUTPUT statement.. ... View more

I find it difficult to think that you are going to get better performance in SAS than running your join in a Terradata pass thru. If Terradata is running much faster with a monthly query, try using your macro to generate the Teradata with a series of UNION ALL joins, then bring the resultant table back to SAS. I personnally find the optimizer in Terradata less effective than that of Oracle (IMHO) so performance is very dependant on your query matching the stucture of the indecies. ... View more

If you do not use an attribute or length statement the new character variable will be set to the length of the first value encountered. ... View more

My guess at this is that the path to the picture is correct for the SAS program, but not for the URL in the browser. The picture is not imbedded, only a link to the location, which the browser must be able to find. ... View more

Here are a couple of code snippets that should help. ods tagsets.excelxp options(sheet_name="" frozen_rowheaders='4' frozen_headers = '5' compute comme; if comme = 'Total' then call define(_ROW_,'STYLE','style={background=#B6DDE8}'); endcomp; ... View more

Based on what you are showing, I would create a format using my meta-table as a cntrlin data. then recode based on the format. ... View more

The best approach here is to create a temporary table in DB2 to do the join. ... View more

Are you running this in SAS or as a pass-thru. This is a SAS function which will not work if you run in a pass-Thru ... View more