I have a data set as shown below:
ID | filing number |
---|---|
2115389 | 2012-09-13 |
2115389 | 2011-10-06 |
2115389 | 2008-04-09 |
11765414 | 2011-08-08 |
11765414 | 2011-07-11 |
16316120 | 2014-07-23 |
16316120 | 2009-12-18 |
How would i compute a third colum called COUNT?
ID | filing number | count |
---|---|---|
2115389 | 2012-09-13 | 1 |
2115389 | 2011-10-06 | 2 |
2115389 | 2008-04-09 | 3 |
11765414 | 2011-08-08 | 1 |
11765414 | 2011-07-11 | 2 |
16316120 | 2014-07-23 | 1 |
16316120 | 2009-12-18 | 2 |
If you want the result shown in your last example, it will be a lot easier to do it using a sort and data step. e.g.;
proc sort data=need;
by 'Duns Number'n 'UCC Filing Dt'n;
run;
data want;
set need;
by 'Duns Number'n;
if first.'Duns Number'n then Count=1;
else Count+1;
run;
proc sql;
create table WANT as
select *,
COUNT(*) as COUNT
from HAVE
group by ID,
FIL;
quit;
Hi
This is the way i wrote the code:
PROC SQL;
CREATE TABLE WORK.NAME AS
SELECT t1.'IDNumber'n,
t1.'Business Name'n,
t1.'Secured Party Name'n,
t1.'Filing Number'n,
t1.'Collateral Type'n,
t1.'Filing Type'n,
t1.'Filing Dt'n,
COUNT(*)as Count
FROM NEED t1
GROUP BY t1.'ID Number'n,
t1.'UCC Filing Dt'n
ORDER BY t1.'ID Number'n,
t1.'UCC Filing Dt'n DESC;
QUIT;
But i do not get the output like the one above.
Thanks,
Thomas
You select 'IDNumber'n but group and order by 'ID Number',n
Could that be your problem?
Hi Arthur,
I have an example showing what the output should look like.
Thanks,
Thomas
Arthur is correct, you have this line:
SELECT t1.'IDNumber'n,
Note that there is no space between ID and Number.
GROUP BY t1.'ID Number'n,
This is with a space.
Then IDNumber and Business Name do not appear in your spreadsheet screenshot.
Then you don't DESC on a group by.
Thirdly I would suggest getting your data out of Excel and into SAS, i.e. a dataset, then working with the dataset. Excel is not a useful tool for any operation and I would avoid it entirely.
Finally, the principal is that you group by the columns you want to count the distinct values of, so group by and select should be the same. If thats not what you want, i.e. it looks like you want more columns in the select than the group by I would suggest you subquery the count and left join it back on. I am out of office now for a few days, so good luck.
If you want the result shown in your last example, it will be a lot easier to do it using a sort and data step. e.g.;
proc sort data=need;
by 'Duns Number'n 'UCC Filing Dt'n;
run;
data want;
set need;
by 'Duns Number'n;
if first.'Duns Number'n then Count=1;
else Count+1;
run;
Hi Arthur,
Just a small change
data want;
set need;
by 'Duns Number'n;
if first.'Duns Number'n then Count=0;
else Count+1;
run;
Thanks,
Thomas
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.