topic Re: Duplicates by group in SAS Programming

Duplicates by group

CG1 — Thu, 27 Oct 2016 13:30:49 GMT

Hi team,

I am running below query

PROC SQL ;
select distinct prod_id,count(ord_num),ord_dt format=MONYY5. as dt
from hcl_mdm.fact_pub_subs_mdm
group by prod_id,ord_dt;
quit;

in this I need to extract prod id waise and MON-YYYY wise data. but above queyr is giving wrong output.

Could you please help me to resolve this

Re: Duplicates by group

ballardw — Thu, 27 Oct 2016 13:44:09 GMT

How is it giving "wrong output"? No output, error message, unexpected value? If the values are not as expected then you need to provide information about the input data and the result and which variable(s) or records are getting the unexpected result.

I am going guess that it has something to do with the variable ORD_DT and not showing in the correct date format. My guess from the variable name ending of DT is that the varaible is a DateTime variable. If so then the the value is recorded in seconds and a date format such as MONYY expects the value to be days and the result either does not display or is showing a date much larger than expected.

If that is the case then you want to use: DATEPART(ord_dt) as dt format=Monnyy5.

Re: Duplicates by group

CG1 — Thu, 27 Oct 2016 14:02:44 GMT

Hi Below is the sample data

PROD_ID	Ord_dt	ord_num
010000000Z	Apr-16	2
010000000Z	Apr-16	1
010000000Z	Apr-87	21
010000000Z	Apr-87	22
010000000Z	Apr-88	29
020000000Z	Apr-88	23
020000000Z	Apr-90	33
020000000Z	Apr-90	61
020000000Z	Apr-16	10
020000000Z	Apr-16	20

output should be

Prod_id	MONYY	Count
010000000Z	Apr-16	3
010000000Z	Apr-87	43
010000000Z	Apr-88	22
020000000Z	Apr-88	23
020000000Z	Apr-90	94
020000000Z	Apr-16	30

PROC SQL ;
select distinct prod_id,count(ord_num),ord_dt format=MONYY5. as dt
from hcl_mdm.fact_pub_subs_mdm
group by prod_id,ord_dt format=MONYY5.;
quit;

can you please correct the above query.

Thanks.

Re: Duplicates by group

ballardw — Thu, 27 Oct 2016 14:06:45 GMT

Looks like you want

sum(ord_num),

instead of

count(ord_num),

count does exactly what its name implies: it returns the number of times the variable was not missing for the combination of the group by variables.

Re: Duplicates by group

CG1 — Thu, 27 Oct 2016 14:51:19 GMT

Hi,

I am getting issue while grouping the record

group by prod_id,ord_dt format=MONYY5.;

I am not getting issue with sum or count.

Please guide me how to convert date into MONYY character in group by statement

Re: Duplicates by group

ballardw — Thu, 27 Oct 2016 18:59:47 GMT

Is your current variabe text or numeric?

If it is currently a SAS date valued numeric and you want an actual text variable then you have to explicitly convert it with something like:

put(datevariablename, MONYY5.) as Textdatevariablename

Re: Duplicates by group

PGStats — Thu, 27 Oct 2016 20:44:09 GMT

I guess you either want

PROC SQL ;
select 
	prod_id,
	count(ord_num) as count_ord_num,
	intnx("MONTH", ord_dt, 0) format=MONYY5. as dt 
from hcl_mdm.fact_pub_subs_mdm
group by prod_id, calculated dt;
quit;

PROC SQL ;
select 
	prod_id,
	count(ord_num) as count_ord_num,
	intnx("MONTH", datepart(ord_dt), 0) format=MONYY5. as dt 
from hcl_mdm.fact_pub_subs_mdm
group by prod_id, calculated dt;
quit;

depending if ord_dt is a SAS date or a SAS datetime.

Re: Duplicates by group

Ksharp — Fri, 28 Oct 2016 04:12:24 GMT

or proc freq.

proc freq .....

table prod_id*ord_dt/out=want list ;

format ord_dt MONYY5.;

run;