topic Re: SAS SQL update iterative tables in SAS Procedures

SAS SQL update iterative tables

Arimitra — Thu, 07 Mar 2013 03:26:56 GMT

Hi All,

I have a table of 1500 observations with Subcategory and their Sales value. I want to find out the count of each subcategory group by subcategory in one table.

In total there are 6 unique subcategories.

proc sql noprint;

count(distinct Subcategory)

candy_sales_summary;

distinct(Subcategory)

:C1 - :C%left(&n)

candy_sales_summary;

;

%put &n;

%put &C3;

I am unable to complete the below code.

%macro calc;
%let i = &n;
%let j = %eval(&n - 1);
%do %until (&i ge &j);
proc sql;
create table Test as
select Subcategory,count(Subcategory) as Units
from candy_sales_summary
where Subcategory in ("&&C&i")
group by Subcategory

union all

select Subcategory,count(Subcategory) as Units
from candy_sales_summary
where Subcategory in ("&&C&j")
group by Subcategory
;
quit;
run;
%end;
%mend calc;
%calc;

Is there any way in sql where I can iteratively append the count of each subcategory in one table

For example when I run this, I get count of only two, when I need all 6 in one table

proc sql;

select Subcategory,count(Subcategory) as Units

from candy_sales_summary

where Subcategory in ('Mixed')

group by Subcategory

union all

select Subcategory,count(Subcategory) as Units

from candy_sales_summary

where Subcategory in ('Gum')

group by Subcategory

;

quit;

Please help

Re: SAS SQL update iterative tables

Arimitra — Thu, 07 Mar 2013 03:30:57 GMT

I beg you pardon for the typo error. When I counting the distinct subcategories which I dont know in advance I am using the below:-

proc sql noprint;

select count(distinct Subcategory)

into :n

from candy_sales_summary;

select distinct(Subcategory)

into :C1 - :C%left(&n)

from candy_sales_summary;

quit;

Re: SAS SQL update iterative tables

Reeza — Thu, 07 Mar 2013 03:33:58 GMT

proc sql;

select Subcategory,count(Subcategory) as Units

from candy_sales_summary

where Subcategory in ('Mixed')

group by Subcategory

union all

select Subcategory,count(Subcategory) as Units

from candy_sales_summary

where Subcategory in ('Gum')

group by Subcategory

;

quit;

should be equivalent to the following where you've removed the where clause.

proc sql;

create table want as

select Subcategory,count(Subcategory) as Units

from candy_sales_summary

where Subcategory in ('Gum')

group by Subcategory

;

quit;

You may want to consider proc freq as well.

proc freq data=candy_sales_summary;

table subcategory;

run;

Re: SAS SQL update iterative tables

Arimitra — Thu, 07 Mar 2013 03:54:37 GMT

The intent of this query is I dont know how many subcategories to count. so I would need where clause to query which subcategories to count.

Re: SAS SQL update iterative tables

Tom — Thu, 07 Mar 2013 04:15:07 GMT

Why are you generating macro variables?

What is it that you are actually trying to do?

You do not need to count in advance. Use the automatic macro variable SQLOBS after the select to see how many values were found.

Just set the upper bound on macro variable range larger than the possible answer.

proc sql noprint;

select distinct Subcategory into :C1 - :C999999

from candy_sales_summary

;

%let n=&sqlobs ;

quit;

Re: SAS SQL update iterative tables

Tom — Thu, 07 Mar 2013 04:27:01 GMT

If you have Subcategories shouldn't you have a Category?

See if you can relate you query to the SASHELP.CARS sample dataset.

This query will show me that there are 2 Makes of Hybrid cars and 3 total models of Hybrid cars.

proc sql noprint ;

create table want as

select type,count(*) as num_makes, sum(num_models) as total_models

, make,num_models

from

( select type,make,count(distinct model) as num_models

from sashelp.cars

group by type, make

)

group by type

order by type, make

;

quit;

proc print; run;

Re: SAS SQL update iterative tables

Arimitra — Thu, 07 Mar 2013 04:49:54 GMT

Hi Tom,

Attached is the query, I have tried to create separate tables for each subcategory but I found no way to append them. The output is required in one table.

I am unable to attach the excel file data. Not sure if I have explained my query properly

Please help.

Re: SAS SQL update iterative tables

OscarHernandez — Thu, 07 Mar 2013 07:45:21 GMT

Hi Arimitra, I'm not sure I understood your question but I wrote the attached code that could be useful.

In general, the code does this:
Step 1: Create sample data.

Step 2: Generate a table that shows the count of subcategories and the total sales amount per each subcategory, everything grouped by date. To achieve this result I did the following:

2.1. Extract a list of distinct subcategories values.

2.2. Extract a list of disctinct dates.

2.3. Perform a cross join in order to generate a list with all posible combinations of subcategories per date.

2.4. Calculate a table with the count of subcategories per date and their corresponding total sales amount.

2.5. Perform a left join in order to complement the table from step 2.3 with its matching subcategory count and total sales amount. As we don't have subcategories for all dates you'll get a missing value for those cases.

You may find this process long, and your original question was related to having one single query to achive the result. In response to this question, you can take a look on the next step.

Step 3: This piece of code is a way to have a single query to do the previous steps, it involves subqueries and Cartesian products, but it is NOT a viable solution and I wouldn't suggest by any means use of it, because of the mentioned Cartesian products and remerging (iterative) processes involved. It is not a good practice and you shouldn't consider it for a real implementation, in fact, step 2.3 will generate a Cartesian product so please keep in mind only steps 1 and 2, they can be considered as a possible solution for this example.

Best Regards,

Oscar

Re: SAS SQL update iterative tables

UrvishShah — Thu, 07 Mar 2013 09:11:20 GMT

Hi Arimitra,

I think it quite easy by using DATA step rather than complex query...Based on your .JPG file, the output you want is the Total Count of distinct subcategories as well as SUM of distinct categories...

Please try the following SAS Code...

proc sort data = have;

by subcategory;

run;

data want;

set have;

by subcategory;

if first.subcategory then do;

count = 0;

total_sales = sales;

end;

else do;

count + 1;

total_sales + sales;

end;

if last.subcategory;

run;

And ya one more thing to say in this case is that if you supply COUNT(DISTINCT Subcategory) then SAS has to two pass through the data that is one time it will remove the duplicates and then in second pass it will count...So of cource if you have more subcategories then it will take much and muchhh time....So either use Proc Freq or Proc Sort followed by Data Step...

Thanks,

Urvish