making a subset of 1000 obs from a excel file....

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

making a subset of 1000 obs from a excel file....

Hi All,

I get a sales data in csv file on daily basis. Its has N number of observation and what I need to do is, sub-setting the observation and making n number of new files with having 1000 observations max.

For example:

Days          Obs in CSV file

day 1          5897

day 2          1478

day 3          10524

........          .......

.......          ........

day 31     n

Here what I need to do on daily basis is,

for Day1 the number of obs are 5897 which needs to be broken into 1000+1000+1000+1000+1000+897

similarly for every day.

Can anyone help me to create a macro that does the job efficiently and effectively. For the time being I use _n_ automatic variable and IF...THEN condition with OUTPUT option but its very lengthy way to achieve the result.

Regards

Anand


Accepted Solutions
Solution
‎09-23-2014 04:32 PM
Respected Advisor
Posts: 4,927

Re: making a subset of 1000 obs from a excel file....

Posted in reply to AnandSahu

Try something simple like:

%let myFile=myData;

data _null_;

infile "&sasforum\datasets\&myFile..csv";

input;

outputFile = cats("&sasforum\datasets\&myFile.",ceil(_n_/1000),".csv");

file dummy filevar=outputFile;

put _infile_;

run;

PG

PG

View solution in original post


All Replies
Super User
Posts: 5,431

Re: making a subset of 1000 obs from a excel file....

Posted in reply to AnandSahu

First PROC IMPORT.

Then use SAS file functions to retrieve no obs.

Then use macro programming to conditionally/dynamically create output files.

May I ask what's this for? I'm having a hard time seeing any (sensible) business requirement behind this.

Data never sleeps
Frequent Contributor
Posts: 81

Re: making a subset of 1000 obs from a excel file....

Obviously, I use Proc Import to import the file then assign a observation numbers in a new variable Count by using _n_ but don't find any efficient way to create a macro as this needs to be done on daily basis...

Solution
‎09-23-2014 04:32 PM
Respected Advisor
Posts: 4,927

Re: making a subset of 1000 obs from a excel file....

Posted in reply to AnandSahu

Try something simple like:

%let myFile=myData;

data _null_;

infile "&sasforum\datasets\&myFile..csv";

input;

outputFile = cats("&sasforum\datasets\&myFile.",ceil(_n_/1000),".csv");

file dummy filevar=outputFile;

put _infile_;

run;

PG

PG
Frequent Contributor
Posts: 81

Re: making a subset of 1000 obs from a excel file....

thanks PG State, it works fine and thanks a lot for this wonderful macro. It has a great logic Smiley Happy

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 292 views
  • 2 likes
  • 3 in conversation