topic Re: import csv YYMM date in SAS Programming

import csv YYMM date

cstarkey — Thu, 25 Mar 2021 02:46:39 GMT

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.

Re: import csv YYMM date

japelin — Thu, 25 Mar 2021 03:38:27 GMT

Could you tell me what M1, M2, and M3 stand for?

Re: import csv YYMM date

Tom — Thu, 25 Mar 2021 03:57:43 GMT

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

Re: import csv YYMM date

cstarkey — Thu, 25 Mar 2021 04:00:15 GMT

Month 1 = Jan, month 2 = Feb, etc...

Re: import csv YYMM date

cstarkey — Thu, 25 Mar 2021 05:27:47 GMT

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!