Good evening SAS community.
I am very new to SAS and would love some help on developing some code. I'm sure this might be very simple for anyone with experience and I thank everyone in advance for sharing the knowledge.
I have a file that is broken out by company, year, country, product code and total sales. I created a very simple dummy file of what my real data looks like below. In short, I am trying to summarize the file so I get output for each company by year that shows me the total total sales that were in the US by company and that were outside of the US by company.
I would love the output to look like something like this:
Company | Year | Domestic - code1 | Domestic - code2 | Domestic - Code 3 | Foreign - Code 1 | Foreign - Code 2 | Foreign - Code 3 |
amazon | 2003 | 4053 | 39438 | 3438 | 39492 | 8382 | 83292 |
Here is what my data looks like. I included it in excel too in case that is more helpful!
Company | Year | Country | Code | Total |
Amazon | 2004 | USA | 1 | 142 |
Amazon | 2003 | USA | 2 | 172 |
Amazon | 2004 | USA | 2 | 178 |
Amazon | 2006 | USA | 1 | 158 |
Amazon | 2003 | USA | 3 | 177 |
Amazon | 2005 | Argentina | 4 | 146 |
Amazon | 2004 | Cuba | 3 | 182 |
Amazon | 2005 | England | 2 | 188 |
Amazon | 2006 | France | 1 | 110 |
Amazon | 2005 | Canada | 4 | 176 |
Amazon | 2005 | Mexico | 3 | 113 |
Sony | 2003 | USA | 2 | 184 |
Sony | 2003 | USA | 1 | 148 |
Sony | 2003 | USA | 3 | 177 |
Sony | 2005 | USA | 2 | 178 |
Sony | 2006 | USA | 1 | 184 |
Sony | 2005 | Argentina | 2 | 122 |
Sony | 2006 | Cuba | 4 | 129 |
Sony | 2006 | England | 2 | 109 |
Sony | 2003 | France | 3 | 103 |
Sony | 2004 | Canada | 3 | 158 |
Sony | 2005 | Mexico | 1 | 187 |
Hello @anweinbe and @Astounding and everyone else who might be reading along
There is another thread on this exact topic at
https://communities.sas.com/t5/SAS-Programming/Summarization-Help/td-p/591335
Let's discuss it in one thread; not this one, but the other one.
I think I'm missing something obvious. Perhaps I'm not specifying my table correctly in the "data want;" part?
PROC IMPORT DATAFILE="/home/creighton/anweinb1/Pensions/ExcelTest1.xlsx"
OUT=WORK.Test_Data1
DBMS=XLSX
REPLACE;
RUN;
PROC PRINT WORK.Test_Data1; RUN;
data want;
set have;
if country='USA' then category='Domestic';
else category = 'Foreign';
run;
proc summary data=want nway;
var total;
class category company year code;
output out=summarized sum=;
run;
You need to use the actual name(s) of your data sets.
In the same code I posted, "have" is just the name we typically assume will be the data that you have already. Since you imported the data and called it Test_Data1, you need to change the SET statement accordingly:
set Test_Data1;
Hello @anweinbe and @Astounding and everyone else who might be reading along
There is another thread on this exact topic at
https://communities.sas.com/t5/SAS-Programming/Summarization-Help/td-p/591335
Let's discuss it in one thread; not this one, but the other one.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.