How do I summarize the following data appropriately

[anweinbe]

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

[Astounding]

It's easier if you first create a variable to categorize foreign versus domestic:

data want;
set have;
if country='USA' then category='Domestic';
else category = 'Foreign';
run;

Next, summarize the data:

proc summary data=want nway;
var total;
class category company year code;
output out=summarized sum=;
run;

Examine the results and see if you still want to reformat the numbers.

[anweinbe]

I think I'm missing something obvious. Perhaps I'm not specifying my table correctly in the "data want;" part?

Here is my complete code:

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;

[Astounding]

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;

[PaigeMiller · Accepted solution]

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.