Thanks a lot, I will do that ... View more

To allow SAS code to use non-standard names for datasets (members) you need to the VALIDMEMBERNAME option to EXTEND.  But even then there are some characters, like / , that are not allowed.   You mentioned that the data is in a database.  Use a pass through query to access the data. For example if you have a libref defined that points to some external database, like ORACLE, libname mydb oracle ... ; You can use that LIBREF in SQL code like this. proc sql; connect using mydb; create table want as select * from connection to mydb (select * from "mygoofy or long table name" ); quit; ... View more

Nobody knows Junior Samples' phone number.   ... View more

One more (untested) variation came to mind:   druguse2 = ^^ marijuanause2 + 2 * ^^illegal_druguse2; ... View more

Nobody is going to download those files and nobody (certainly not me) is going to understand or undertake your problem as written.    A little bit more explanation, a working data step, an example of desired results, and things you've tried will g a long way toward helping someone help you.   ... View more

If you simply must have an SQL solution use DISTINCT.   Using BallardW's data statement you can do    proc sql; select distinct a.* from example a; quit; to get x y z 1 2 3 2 3 4 2 3 5 3 4 5   ... View more

Yep.     This { item ,item ,item }   doesn't work. Never loads.    I think I do need { item, item, item }   for the code to like and display it.    I've tried every rearrangement of  "key":' dataset_value :$quote. /',   I can think of with no joy.    "key":' dataset_value :$quote.  ,/'  "key":' dataset_value :$quote. ' ,/ "key":' dataset_value :$quote. ' , /n   How do I do it?  Looking at you Tom.    ... View more

Yes. It doesn't matter now but I would have wagered on where year='2008'; ... View more

Or here a 3rd option. data have; input group $ rate; datalines; A 35000 B 746 C 462158 D 9534 E 760449 A 31.523 B 30.64 C 12.701 D 24.339 E 18.9111 ; proc fcmp outlib=work.fcmp.funcs; function f_real_pct(in) $16; if ceil(in)=in then out=put(in,f16.); else out=catx(' ',put(in,f16.1),'%'); out=strip(out); return(out); endsub; run;quit; options cmplib=work.fcmp; proc format; value real_pct other=[f_real_pct()] ; run; proc print data=have; format rate real_pct.; run;   ... View more

data have; infile cards delimiter='|'; /*from is parent, to is child*/ input from $ to $ ; cards; 2 | 4 2 | 5 2 | 6 2 | 8 4 | 7 4 | 11 6 | 9 6 | 10 6 | 12 10 | 15 10 | 16 13 | 14 16 | 17 99|2 7|4 100|20 ; run; data full; set have end=last; if _n_ eq 1 then do; declare hash h(); h.definekey('node'); h.definedata('node'); h.definedone(); end; output; node=from; h.replace(); from=to; to=node; output; node=from; h.replace(); if last then h.output(dataset:'node'); drop node; run; data want(keep=node household); declare hash ha(ordered:'a'); declare hiter hi('ha'); ha.definekey('count'); ha.definedata('last'); ha.definedone(); declare hash _ha(hashexp: 16); _ha.definekey('key'); _ha.definedone(); if 0 then set full; declare hash from_to(dataset:'full',hashexp:20,multidata:'y'); from_to.definekey('from'); from_to.definedata('to'); from_to.definedone(); if 0 then set node; declare hash no(dataset:'node'); declare hiter hi_no('no'); no.definekey('node'); no.definedata('node'); no.definedone(); do while(hi_no.next()=0); household+1; output; count=1; key=node;_ha.add(); last=node;ha.add(); rc=hi.first(); do while(rc=0); from=last;rx=from_to.find(); do while(rx=0); key=to;ry=_ha.check(); if ry ne 0 then do; node=to;output;rr=no.remove(key:node); key=to;_ha.add(); count+1; last=to;ha.add(); end; rx=from_to.find_next(); end; rc=hi.next(); end; ha.clear();_ha.clear(); end; stop; run; ... View more

Your "want" makes no sense to me.  Are there more decision rules? Why are there no Total_Customer_OSBal balances for customer 2 for Feb, Mar, Apr, and May?    ... View more

Thank you all for your help! Apologies for not posting the problem clearly.  ... View more

Yes, we have learned using proc means and summary.. haven’t learned about sql yet. Thank you! ... View more

In a data step if want related records, such as a matching ID variable, to be used then you would MERGE by the matching variable(s). Which will typically involve sorting both data sets by those variables first.   The behavior of Merge for same named variable that are not the by variables is that one value will overwrite others. Which exactly depends on order sets on the MERGE statement. Generally the rightmost ( or last) data set mentioned on the merge statement will contribute the value. Merge however does not work well if the matching variables have multiples in both data sets. If need both of the same named variable values for other than replacement then you RENAME the variable in a data set option for one (or more) of the data sets. Then use appropriate named variable for your purpose.   If the records in the 'base' data set, the one you are updating, are uniquely identified (meaning no duplicates) of the key variables you can use the UPDATE instead of Merge which has some options that behave differently than merge would. Such as all of the values of 'transaction' set, the one with the new values, all update the same record. Merge with multiple records for the By variable would have multiple output records.   But example of what you actually are working with and desired output may be needed as the behavior of subqueries is not directly available with merge and may require Hash. ... View more