topic Re: Transforming data in New SAS User

Transforming data

togglefroggle — Fri, 18 Dec 2020 17:27:34 GMT

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 45

Baker County 54

Brenton County 21

Brenton County 18

Brenton County 105

Brenton County 108

Coos county 21

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.

Re: Transforming data

ballardw — Fri, 18 Dec 2020 17:36:34 GMT

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.

Re: Transforming data

togglefroggle — Fri, 18 Dec 2020 17:51:19 GMT

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

Re: Transforming data

ballardw — Fri, 18 Dec 2020 18:34:15 GMT

@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.