Modifying a SAS dataset ?

Reply
New Contributor
Posts: 3

Modifying a SAS dataset ?

Hey ya'll - so i'm working on a project for school using SAS at FSU, but I'm currently stuck on editing the initial data so that it's more usable. 

 

So here's my current data set's variables -

 

| Year | Ethnicity | Sex | Cause_of_Death | Count | Percent |

 

Year = 2007 to 2011

Ethnicity = H, NHW, NHB, API
Sex = MALE or FEMALE
Cause_of_Death = one of 32 non-numberic values
Count = non-negative number

Percent = non-negative number

 

What i'm trying to do is modify the data so that the variables are:

 

| Year | Cause_of_Death | H_m | H_f | NHW_m | NHW_f | NHB_m | NHB_f | API_m | API_f |

 

Year = 2007 to 2011
Cause_of_Death = one of 32 non-numberic values

H_m = (value of count that matches MALE, H, Year & Cause_of_Death)

H_f = (value of count that matches FEMALE, H, Year & Cause_of_Death)

etc. 

 

not sure how to go about this, i've been trying to figure it out for a lil bit right now, any help would be appreciated!

Grand Advisor
Posts: 17,356

Re: Modifying a SAS dataset ?

That's generally not considered a good format to store your data in. Why do you want your data in that format?
New Contributor
Posts: 3

Re: Modifying a SAS dataset ?

the original data was taken from a gov. site, and I guess i'm trying to change the data into something that's a little more numerical, cuz that's what i've been used to with most of my econometric stuff (i'm a prior gretl user xD) 

 

is there an easier way to approach this? 

Grand Advisor
Posts: 17,356

Re: Modifying a SAS dataset ?

What do you want to do with the data? Besides change it into a different format, that would be considered unusable by data analysts everywhere.

Tidy Data
http://vita.had.co.nz/papers/tidy-data.pdf
New Contributor
Posts: 3

Re: Modifying a SAS dataset ?

I see what you're saying now - I originally wanted a way to use the data more effectively - because my graphs we're distorted and couldn't figure out why, but I just went through the data again and just found out that  my CSV import caused my variable "count" to be considered a nonnumerical variable , so that's actually what i'm trying to fix now. 

 

not sure how to fix it: here's what i have:

 

PROC IMPORT DATAFILE=REFFILE
	DBMS=CSV
	OUT=NYdata;
	GETNAMES=YES;
RUN;


 

Respected Advisor
Posts: 3,836

Re: Modifying a SAS dataset ?

Proc Import determines the required data type by scanning through your source text file. I assume you end up with character instead of numeric because one of the "cells" in your source contains some character value - so SAS must assume the source is character.

 

You get much more control of how to read data using a data step with INFILE/INPUT. If you have SAS EG (not sure what SAS Studio offers) then you can use the Import Wizard, generate the data step and then amend it as you need.

Super User
Super User
Posts: 6,347

Re: Modifying a SAS dataset ?

Don't use PROC IMPORT to read a CSV file that you already know the structure of. There is no need to make SAS guess at how to read the data when you can just tell it.

data NYdata;
  infile reffile dsd dlm=',' truncover firstobs=2;
  length Year 8 Ethnicity $3 Sex $5 Cause_of_Death $50 Count 8 Percent 8 ;
  informat count percent comma32. ;
  input year -- percent ;
run;
Grand Advisor
Posts: 17,356

Re: Modifying a SAS dataset ?

You are looking for a transpose, if you want to do it though.

http://www.ats.ucla.edu/stat/sas/modules/ltow_transpose.htm
Super User
Super User
Posts: 6,347

Re: Modifying a SAS dataset ?

Use PROC TRANSPOSE.

 

proc sort data=have ;
   by year cause_of_death ethnicity sex ;
run;

proc transpose data=have out=want delim=_ ;
  by year cause_of_death ;
  id ethnicity sex ;
  var count;
run;
Ask a Question
Discussion stats
  • 8 replies
  • 371 views
  • 0 likes
  • 4 in conversation