The following code choose the first '1' if you have multiple, tweak it to accomodate your real data: data have; input Obs ID A B C ; cards; 1 1 1 . . 2 1 . 1 . 3 1 1 . . ; data want; set have; array _t a--c; length category $ 10; category=vname(_t(whichn(1,of _t(*)))); run; ... View more

Hmm. Something isn't quite right about your USM file. This is mine, mine is 2014 Tiger as well. Yours doesn't even have city columns. So you may want to check your lookup table.   I am aware of you have a big data to tackle, so this is just FYI in case you need it in the future. The following link provides the most updated golden standard geocoding for financial industry: https://geomap.ffiec.gov/FFIECGeocMap/GeocodeMap1.aspx   They don't provide API for automated search and web scrapping, but google will help. ... View more

Understood what you are saying, but just to confirm, otherwise I will be utterly lost, is this the actual code you were running?   lookupSTREET=SASHELP.USM;   As this is refering to sashelp library, which by the way, can NOT be reassigned. So are you saying in your real code, you were using 2014 Tiger in a different library, meaning you real code will be using a different non-reserved libname to address that.   Other than that, the only thing I know is that if the city level match is failed , then the city will be left blank. ... View more

It has been a while since I did my last geocoding, I can only speak from my impression. You may need to download the most current TIGER file from census site to do a thorough geocoding. The dataset in sashelp is just for demo, I believe. It is neither complete nor current. Please reference this paper for details: http://support.sas.com/resources/papers/proceedings10/332-2010.pdf ... View more

Add new computed column -> advanced expression -> sum(playertier1='classic') -> enter the name of your new column -> done. ... View more

Could be as straightforward as:   proc sql; select sum(playtier1='classic') as classic_ct from have ; quit; ... View more

  Q1: How do you identify/differentiate good ID1 from those are bad. Q2: If you have multiple goods and some bads, which one of the goods you want to use to populate the bads. Q3: Will the goods always appear before the bads? Q4: Have your data presorted by ID2 or at least you know ID2 are clustering together?   Anyway, I made some reasonable guess, and here are some codes to get you going ( Sorry, I haven't read through your code): data have; input (id1 id2) (:$8.); cards; 1 123 1.1 123 1.2 234 1.2 234 ; /*using 2XDOW is to assume that you are not sure if goods appear before bads, otherwise, code can be simpler*/ /*this is to assume if anything but number appears will be redeemed as bad*/ data want; do until (last.id2); set have; by id2 notsorted; if notdigit(strip(id1))=0 then _id1=id1; end; do until (last.id2); set have; by id2 notsorted; if notdigit(strip(id1))>0 then id1=_id1; if missing(_id1) then id3=sum(id3,1); output; end; drop _id1; run; ... View more

At the risk of being obnoxious, what I was trying to say is not you can generate the same results, rather, is that your SQL logic is not to mirror 'first.', as you have heavily depended on some other assumptions. What if this: data have; input Stdid Marks; cards; 1 36 1 34 1 43 2 54 2 60 2 65 ; ... View more

Won't be the same if there is dups data have; input Stdid Marks; cards; 1 34 1 34 1 43 2 54 2 60 2 65 ; ... View more

I am second to @Reeza's comments, you can't get it done using Proc SQL without involving some 'extraordinary measures', I mean 'undocumented/unsupported feature', such as the following: proc sql; create table test as select * from have group by stdid having monotonic()=min(monotonic()) ; quit; monotonic() is not officially supported by SAS, use it at your own risk. However, this implementation is the closest I can think of to your data step counterpart 'first.'.    ... View more

So if you have more than 2 time points, what happens when the conditions goes back and forth, such as 'better-worse-better"? Or all you care is to compare starting point and ending point? ... View more

@Ksharp, If you already have the index loaded, you don't need hiter at all. Just 'rc=h.find(key:10)' will be sufficient. ... View more