Hello everyone,
I am trying to analyze changes in unemployment rate in a city over time (2000 - 2020) using SAS. A state government website reports unemployment rate data by city between 2000 and 2020 - please see the attached file for a snapshot (sheet 1: "Import_Unemployment").
I would like to import the csv file and then to be able to convert it into a SAS dataset which looks like "Desirable Output" in the second sheet, i.e., panel dataset. I have tried using proc import and other techniques but none worked. Does anybody know how to figure this out?
I would be grateful if you could provide me with any tips regarding this.
Thank you in advance.
Kishiyo
PROC IMPORT OUT= WORK.Test
DATAFILE= "<your csv file along with path>"
DBMS=CSV REPLACE;
GETNAMES=YES;
DATAROW=2;
RUN;
data new;
set test;
retain year;
Value2=input(tranwrd(value,'%',''),best12.);
if anyalpha(var1)=0 then year=var1;;
if anyalpha(var1)>0 then output;
run;
proc sort data=new;
by year;
proc transpose data=new out=newt(drop = _name_);
by year;
var value2;
id var1;
;
run;
Hope this helps
When you need help importing a CSV file, you need to post that, not the Excel representation, which is useless.
It is usually sufficient to open the csv file with an editor (not with Excel!), and copy/paste the first few lines into a window opened with the </> button.
I might try something along these lines:
data want; infile "path and filename.csv" dlm=','; informat dummy $1.; input year /dummy variable_a /dummy variable_b /dummy variable_c /dummy variable_d ; drop dummy; run;
The / in the input statement this way says "read from next line". The Dummy variable holds a not needed part of the "variable name". If your data does not change in order as implied by your example and always has 4 lines of variable a to d then this should read the data into that form.
If your data was manually entered into Excel then stop it if you want "panel" data like this.
Dear Ballardw,
Thank you for your help! I found your examples very helpful.
Kishiyo.
PROC IMPORT OUT= WORK.Test
DATAFILE= "<your csv file along with path>"
DBMS=CSV REPLACE;
GETNAMES=YES;
DATAROW=2;
RUN;
data new;
set test;
retain year;
Value2=input(tranwrd(value,'%',''),best12.);
if anyalpha(var1)=0 then year=var1;;
if anyalpha(var1)>0 then output;
run;
proc sort data=new;
by year;
proc transpose data=new out=newt(drop = _name_);
by year;
var value2;
id var1;
;
run;
Hope this helps
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: