topic Re: Proc import to import file with different date in SAS Programming

Proc import to import file with different date

imdickson — Wed, 27 Feb 2019 14:45:22 GMT

Hi all. I was given some csv files with the following naming convertion:

re_jan2018.csv
re_feb2018.csv
re_mac2018.csv
re_apr2018.csv
re_may2018.csv
re_jun2018.csv
re_jul2018.csv
re_aug2018.csv
re_sep2018.csv
re_oct2018.csv
re_nov2018.csv
re_dec2018.csv
re_jan2019.csv
re_feb2019.csv

There will be a new csv file with new month, monthly, in above naming convention

The problem is, user will key in the period in yymmn6. format, which will look like this, everytime:

201801 <- this indicate jan2018
201902 <- this indicate feb2019

My expectation is as below:

1) User will input period, eg, 201808
2) script will then change that to aug2018 and assign this into proc import file statement to import aug2018.csv file

I want to import the csv file according to user prompt in above format.

I have following code (that doesnt work)

%let period=201812;
%macro convertdate();
data convertdate;
year=substr("&period",1,4);
month=substr("&period",5,2);
if month='01' then newmonth='jan';
if month='02' then newmonth='feb';
if month='03' then newmonth='mac';
if month='04' then newmonth='apr';
if month='05' then newmonth='may';
if month='06' then newmonth='jun';
if month='07' then newmonth='jul';
if month='08' then newmonth='aug';
if month='09' then newmonth='sep';
if month='10' then newmonth='oct';
if month='11' then newmonth='nov';
if month='12' then newmonth='dec';

run;

/*Assign convertdate the only distinct record into macro, then set as the data step for proc import*/
%if month='01' %then %do;
	%let newmonth=jan;
%end;
/*if month='02' then newmonth='feb';*/
/*if month='03' then newmonth='mac';*/
/*if month='04' then newmonth='apr';*/
/*if month='05' then newmonth='may';*/
/*if month='06' then newmonth='jun';*/
/*if month='07' then newmonth='jul';*/
/*if month='08' then newmonth='aug';*/
/*if month='09' then newmonth='sep';*/
/*if month='10' then newmonth='oct';*/
/*if month='11' then newmonth='nov';*/
/*if month='12' then newmonth='dec';*/


/*proc import and remaining transformation from existing script*/
proc import out=rmr_raw_source_jan2018
  file="/sasdata/source/user_files/re_&newmonth.2018.csv"

  dbms=csv replace;
  sheet="re_&newmonth.2018";
  getnames=no;
  dbsaslabel=none;
run;

%mend;
%convertdate;

and my log:

WARNING: Apparent symbolic reference NEWMONTH not resolved.

Can anyone tell me a better way of doing it?

Re: Proc import to import file with different date

RW9 — Wed, 27 Feb 2019 14:37:24 GMT

You don't need to code for each month, simply:

%let period=201812;

data _null_;
  call symput('dte',substr(put(input(cats("&period.","01"),yymmdd10.),date9.),3));
run;

proc import file=".../re_&dte..csv"...

You can lower case if need be.

Next up, dont use proc import to import data in a production environment. Write a datastep import (you can take the generated code from the log) and specifically set it per your known data structure. Proc import guesses the data format, so you can end up with different structure each time.

Re: Proc import to import file with different date

PaigeMiller — Wed, 27 Feb 2019 15:16:21 GMT

One of your CSV files has a different format of the file name: re_oct_2018.csv, that underscore between the month and year will be a problem, and my code won't address this.

Next, the idea of mixing and matching data step commands with macro commands will work, but you have to be extremely careful when you do this. It is very easy to try to use data step commands on macro variables, or macro variables where you really mean data step variables, and this is a cause of problems.

Any advice should begin with this: first, get the code working WITHOUT macros and WITHOUT macros variables on one or two cases, and then turning it into a macro ought to be much easier. You haven't done this.

Lastly, learn to use the SAS date/time functions, formats and informats and your life will be much much easier than writing out code for each month.

Here's a simple solution for the case where period has the value 201812

%let period=201812;

data _null_;
	monthyr=input("&period",yymmn6.);
	call symputx('month',cats(put(monthyr,worddate3.),year(monthyr)));
run;
%put &=month;

Re: Proc import to import file with different date

imdickson — Thu, 28 Feb 2019 02:50:11 GMT

Thank you