Transforming data

[togglefroggle]

I am trying to transform my data using sas so I can use it for other applications, my data is a list of all counties in Oregon and an ID number, here is an example:

County name        ID

Baker County       21

Baker County       21

Baker County       45

Baker County       54

Brenton County    21

Brenton County    18

Brenton County    18

Brenton County    18

Brenton County    105

Brenton County    108

Coos county          21

Coos county          76

Coos county          76

Coos county          105

etc..

I want to put my data into this form:

                               18     21   45    54   76   105   108  etc...

Baker County                   2     1      1

Brenton County       3       1                            1       1

Coos County                    1                            1

etc...

Where i have a count for how many of those IDS are in each county, keep in mind that this is an example dataset and my dataset has about 500,000 rows with all counties and around 70 distinct IDs in total, so every county should have at least a few of every ID.

I am having trouble figuring out how to do this so any helps using sas or even if you have an idea of how to do it differently would be much appreciated. Thank you.

[ballardw · Accepted solution]

What file format do the "other applications" expect to use?

Creating output like this is easy:

data example;
 infile datalines dsd;
 input Countyname $ 1-15  ID;
datalines;
Baker County   21
Baker County   21
Baker County   45
Baker County   54
Brenton County 21
Brenton County 18
Brenton County 18
Brenton County 18
Brenton County 105
Brenton County 108
Coos county    21
Coos county    76
Coos county    76
Coos county    105
;

proc tabulate data=example;
   class countyname id;
   table countyname='',
         id=''*n=''
         /misstext=' '
   ;
run;

If the output needs to be a spread or such then you use the ODS to create an output file.

[togglefroggle]

Thank you very much! I need to create into a spreadsheet so you post is very helpful!

[ballardw]

---

@togglefroggle wrote:
Thank you very much! I need to create into a spreadsheet so you post is very helpful!

---

That should be something like:

ODS Excel file="c:\folder\subfolder\myfilename.xlsx";

<proc tabulate code>

ods excel close;

If that doesn't work because you don't have the right licenses you could use ODS CSVALL, with a CSV extension instead of xlsx, to create a CSV file that Excel should be able to open. Depending on use you may then need to save as an xlsx file.

The Path needs to be to a location you have write privileges. If you are working with a server install of SAS you would need to use a path on the server, not your local computer. Then move the file after if needed.