DATA Step, Macro, Functions and more

Duplicates by group

Reply
Occasional Contributor CG1
Occasional Contributor
Posts: 13

Duplicates by group

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

 

Super User
Posts: 11,343

Re: Duplicates by group

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.

Occasional Contributor CG1
Occasional Contributor
Posts: 13

Re: Duplicates by group

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.

Super User
Posts: 11,343

Re: Duplicates by group

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.

Occasional Contributor CG1
Occasional Contributor
Posts: 13

Re: Duplicates by group

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

Super User
Posts: 11,343

Re: Duplicates by group

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

 

 

Respected Advisor
Posts: 4,920

Re: Duplicates by group

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;

Or

 

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.

PG
Super User
Posts: 10,020

Re: Duplicates by group

or proc freq.

 

proc freq .....

table prod_id*ord_dt/out=want list ;

format ord_dt MONYY5.;

run;

Ask a Question
Discussion stats
  • 7 replies
  • 495 views
  • 2 likes
  • 4 in conversation