Import a csv file into SAS and then convert it into a panel dataset

[kishiyo]

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 

[Kurt_Bremser]

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.

[kishiyo]

Dear KurtBremser,

Thank you for your quick response and also for suggestion. I will be more concise in the future when I seek for assistance.

Kishiyo

[ballardw]

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.

[kishiyo]

Dear Ballardw, 

Thank you for your help! I found your examples very helpful. 

Kishiyo. 

[smantha · Accepted solution]

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

[kishiyo]

Dear Smantha,

Thank you so much - it worked very well. I appreciate your timely response and help.

Kishiyo