Well, had to take my word back.  One don't seem to need 2 pass if it is fixed number of rows per ID. So for instance 2rows/id:   data have; input id month var1; datalines; 1 6 2 1 14 1 2 9 3 2 14 1 ; run; data want; set have; by id; _dm=dif(month); _dv=dif(var1); if last.id then do; RateOfChange=_dv/_dm; do month=month-_dm to month; output; end; end; keep id month RateOfChange; run; ... View more

No matter what you have in mind, you will need 2xpass to the data. So 2XDOW seems logical, and it can deal more than 2 rows per id.   data have; input id month var1; datalines; 1 6 2 1 14 1 2 9 3 2 14 1 ; run; data want; do until (last.id); set have; by id; if first.id then do; _from=month; _d1=var1; end; if last.id then do; _to=month; _d2=var1; end; end; RateOfChange=(_d2-_d1)/(_to-_from); do until (last.id); set have; by id; if first.id then do; do month=_from to _to; output; end; end; end; keep id month RateOfChange; run; ... View more

I have little to zero knowledge about PostgreSQL, so there maybe an issue around using double quotes instead of the single quotes. however, if that is the case, it boils down to delivering the right code content to 'pass-thru', for which SAS Macro is invented.  So in the case of your code as following:    AND source = ANY('{&varList.}'::varchar[])    There is another way to deliver it as wrapping everything on the right of the = sign (well, you don't have to wrap everything, this is just an example) into a compound macro variable (and you can also choose to concatenate the contents directly without making new macro variables, this is for easy reading) /*construct your macro variable*/ %let _pre=%str(ANY%(%'{); %let varlist=src1,src2,src3,src4; %let _post=%str(}%'::varchar[]%)) ; %let _out= %unquote(&_pre.&varlist.&_post);   Now your code will be like:   AND source = &_out.;             ... View more

This is to extract and thing after 'with' including 'with',   data test; infile cards truncover ; input var1 $100.; new_var=prxchange('s/.+(with.+)/$1/io',-1,var1); cards; Members United Swaps With Defendants Members United Swaps With Other Parties Southwest Swaps With Defendants Southwest Swaps With Other Parties WesCorp Swaps With Defendants WesCorp Swaps With Other Parties U.S. Central Swaps With Defendants U.S. Central Swaps With Other Parties ; run; ... View more

And Proc Contents with out= option.   ... View more

This will just be a wild guess: in your code: ANY('{src1,src2,src3,src4}'::varchar[])   If you plug in macro variable in place of src1-src4, it wouldn't work as is. the single quote will prevent macro variable from resolving. Try using double quotes instead if Greenplum SQL allows: ANY("{&src1,&src2,&src3,&src4}"::varchar[]) ... View more

Macro variable contains STRING/TEXT. It will store whatever you feed to it. if want '10,000', then you need to feed that into it. So while dealing numeric, you can't directly feed its format to macro variable, instead, you may need to convert it first.   data want; input num; call symputx('val',put(num,comma8.)); datalines; 10000 ; run; %put val=&val;   ... View more

No need for a 'count' variable in Hash setting: data want_h; if _n_=1 then do; declare hash h(dataset:'have(keep=name)', multidata:'y'); h.definekey('name'); h.definedata(all:'y'); h.definedone(); end; set have; h.find(); h.has_next(result:_n_); if _n_ ne 0 then name=cats(name,id); run; ... View more

We do, John. We do SAS tutorials/presentations for clients, and this comes in handy in terms of simulating input files (stream) with their contents exposed the same screen.  ... View more

Thank you for the explanation. I will let others be aware of the system option. ... View more