Hello.
I posted a question about transposing this csv dataset and now I'm stuck at trying to get the date to format correctly. As before, the dataset is like:
Country Counter_Country Type 2001M1 2001M2 2001M3
France US Import 10 11 12
France US Export 4 5 6
France UK Import 1 2 3
France UK Export 2 3 4
US FR IM 3 4 5
US FR EX 5 6 7
US UK IM 4 5 6
US UK EX 5 6 7
My code to import and transpose is:
PROC IMPORT DATAFILE= "C:\file\dot.csv"
OUT= dot_Data
DBMS=csv;
GETNAMES=yes;
proc sort data=dot_data; by Country Counter_Country type;
RUN;
proc transpose data=dot_Data out=dot_long;
by Country Counter_Country type;
*by category notsorted;
run;
This takes the dates (1985M1, 1985M2, etc...) from being variable names and makes a column with the default _NAMES_. The data is one column:
Country Counter_Country Type _NAME_ COL1
France US Import 2001M1 10
France US Import 2001M2 11
France US Import 2001M3 12
France US Export 2001M1 4
France US Export 2001M2 5
France US Export 2001M3 6
etc...
It makes the _NAME_ col format character $8. I need to make it a date format. Amongst other attempts, I've tried:
data dot_temp (rename=(COL1 = Value));
set dot_long;
dte= input(_NAME_,anydtdte32.);
format dte YYMON7.;
run;
but it only create a dte column with a period. Is there any way to import the data as a date format or to change it to a date format? Any help would be appreciated.
Thank you.
Assuming the part after the M is the month number then you can parse the string at the M and using INPUT() to read the month number. You can then use MDY() function to create a date value.
data want;
input string $8.;
date = mdy(input(scan(string,-1,'M'),2.),1,input(string,4.));
format date date9.;
put date=;
cards;
2001M1
2001M2
;
But why not just read the variables the right way to begin with? Why use PROC IMPORT to read a simple text file?
data want;
infile "C:\file\dot.csv" dsd truncover firstobs=2;
input Country :$40. Counter_Country :$40. Type :$10. @;
do offset=0 to 2 ;
date = intnx('month','01JAN2001'd,offset);
input value @ ;
output;
end;
format date yymm7.;
drop offset ;
run;
Result:
Counter_ Obs Country Country Type date value 1 France US Import 2001M01 10 2 France US Import 2001M02 11 3 France US Import 2001M03 12 4 France US Export 2001M01 4 5 France US Export 2001M02 5 6 France US Export 2001M03 6 7 France UK Import 2001M01 1 8 France UK Import 2001M02 2 9 France UK Import 2001M03 3 10 France UK Export 2001M01 2 11 France UK Export 2001M02 3 12 France UK Export 2001M03 4 13 US FR IM 2001M01 3 14 US FR IM 2001M02 4 15 US FR IM 2001M03 5 16 US FR EX 2001M01 5 17 US FR EX 2001M02 6 18 US FR EX 2001M03 7 19 US UK IM 2001M01 4 20 US UK IM 2001M02 5 21 US UK IM 2001M03 6 22 US UK EX 2001M01 5 23 US UK EX 2001M02 6 24 US UK EX 2001M03 7
Could you tell me what M1, M2, and M3 stand for?
Month 1 = Jan, month 2 = Feb, etc...
Assuming the part after the M is the month number then you can parse the string at the M and using INPUT() to read the month number. You can then use MDY() function to create a date value.
data want;
input string $8.;
date = mdy(input(scan(string,-1,'M'),2.),1,input(string,4.));
format date date9.;
put date=;
cards;
2001M1
2001M2
;
But why not just read the variables the right way to begin with? Why use PROC IMPORT to read a simple text file?
data want;
infile "C:\file\dot.csv" dsd truncover firstobs=2;
input Country :$40. Counter_Country :$40. Type :$10. @;
do offset=0 to 2 ;
date = intnx('month','01JAN2001'd,offset);
input value @ ;
output;
end;
format date yymm7.;
drop offset ;
run;
Result:
Counter_ Obs Country Country Type date value 1 France US Import 2001M01 10 2 France US Import 2001M02 11 3 France US Import 2001M03 12 4 France US Export 2001M01 4 5 France US Export 2001M02 5 6 France US Export 2001M03 6 7 France UK Import 2001M01 1 8 France UK Import 2001M02 2 9 France UK Import 2001M03 3 10 France UK Export 2001M01 2 11 France UK Export 2001M02 3 12 France UK Export 2001M03 4 13 US FR IM 2001M01 3 14 US FR IM 2001M02 4 15 US FR IM 2001M03 5 16 US FR EX 2001M01 5 17 US FR EX 2001M02 6 18 US FR EX 2001M03 7 19 US UK IM 2001M01 4 20 US UK IM 2001M02 5 21 US UK IM 2001M03 6 22 US UK EX 2001M01 5 23 US UK EX 2001M02 6 24 US UK EX 2001M03 7
Brilliant, thank you very much! I've mainly dealt with Excel files so Proc Import has always been the go to. The infile method you showed me not only fixed my issues with the dates but also the transpose is no longer even needed. I can't thank you enough!
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 25. 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.