topic Re: Import selective records in SAS Programming

Import selective records

Babloo — Sun, 21 Aug 2016 09:12:08 GMT

I've a CSV file which has last one year (Aug'15 to Jul'16) data. Now my requirement is to import only last 3 months (Jul'16 , Jun'16 & May'16) records without hardcoding. File has a date variable which has values like 21JUN2016 , 20JUL2016 , 31DEC2015 alongside other variables.

Appreciate if someone guide me with the code.

Re: Import selective records

kumarK — Sun, 21 Aug 2016 09:27:38 GMT

can you post some sample have dataset and what columns you want that would be helpful to give better solution. Thanks

Re: Import selective records

Babloo — Sun, 21 Aug 2016 09:43:59 GMT

Since it is a interview question I don't have any example dataset. I need
to import all the variables including the key variable date.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Import selective records

jklaverstijn — Sun, 21 Aug 2016 10:26:45 GMT

How about this:

proc sql;
    create table want as
        select * from have
        where month(datevar) + 3 > (
           select max(month(datevar) from have));
quit;

Hope this helps,

- Jan.

Re: Import selective records

Reeza — Sun, 21 Aug 2016 12:02:55 GMT

@Babloo Shouldnt you complete interview questions on your own? It's not a fair representation of your skill set otherwise.

Re: Import selective records

Ksharp — Mon, 22 Aug 2016 09:11:06 GMT

You'd better post some data to let us test code:


proc sql;
create table want as
 select *
  from sashelp.air
   having date ge intnx('month',max(date),-2) ;
quit;

Re: Import selective records

Babloo — Mon, 22 Aug 2016 14:16:57 GMT

Thanks for the code.

Your code subsetting the data from master SAS dataset (sashelp.air). But my question is to creating a master datset with subset of records. i.e. I will need to create a SAS dataset by importing only selective records.

Re: Import selective records

Ksharp — Tue, 23 Aug 2016 02:34:24 GMT

?? Once you import that CSV file correctly and there is a DATE type variable date , you can use my code ,just replace SASHELP.CLASS with your HAVE dataset.

Re: Import selective records

Babloo — Tue, 23 Aug 2016 05:28:50 GMT

I need to filter the records while importing not after importing

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Import selective records

ballardw — Tue, 23 Aug 2016 19:52:34 GMT

@Babloo wrote:
I need to filter the records while importing not after importing

Good luck with that. If you are supposed to determine the last three months from the contents of the data file without reading it first then I suspect that you may have partially misunderstood the question.

Generically I would have looked at

1) read the data

2) determine the last date in the data set

3) then build a filter and subset the original data.

Or possibly the purpose of the question would be to get you to request clarification of the process.

Re: Import selective records

Reeza — Tue, 23 Aug 2016 21:23:40 GMT

I suspect that you may have partially misunderstood the question.

Interview questions are meant to test both understanding and technical ability so this is a good question according to that.

And yes, it's being misunderstood.

The solution is a single line of code (WHERE clause) in the data input step based on todays date and intnx function.