topic Re: How to split a large dataset by certain requirements? in SAS Procedures

How to split a large dataset by certain requirements?

Neal0801 — Thu, 20 Oct 2016 15:56:49 GMT

data AXPM061204200; 
set a; 
call execute ("data work.data_"||strip(put(_ric,$13.))||"_"||strip(put(date,yymmddn8.))||"; 
               set work.a;if date="||date||" and _ric="||input(_ric,$13.)||";run;"); 
run;

I have a large dataset that contians multiple date (from 20120103 to 20121205) and contracts (_ric= AXPM061204200.c, AXPC061204200.c and so on).

I tried to use "call exectue" to split the dataset and here is my code. It can only split by date, but fail in contract name (_ric). The error was : Variable AXPM061203800 is uninitialized. How could I solve this problem.

Re: How to split a large dataset by certain requirements?

Reeza — Thu, 20 Oct 2016 19:29:43 GMT

If you run it with the following options and examine your log you'll see the exact code that's being created. There's something in the code that's incorrect, but sometimes hard to see in the current format.

options mprint symbolgen;

Re: How to split a large dataset by certain requirements?

Astounding — Thu, 20 Oct 2016 19:42:59 GMT

The code in the IF conditions generates:

and _ric=some_value

But _RIC is a character variable, so you need quotes in the generated statement:

and _ric="some_value"

Re: How to split a large dataset by certain requirements?

ballardw — Thu, 20 Oct 2016 19:53:29 GMT

A couple hints when working with Call execute.

1) It is often easier to define a long string variable for the argument of Call execute and then use some thing like

call execute (string);

2) you can use put statements to check the value of the string to see if things look right.

3) use the CAT functions such as CATS or CATX. The venerable || operator often results in undesired spaces leading or trailing bits of your code.

4. Instead of trying to force:

Data work.data_longnamehere;

set work.if;

If date= "some obnoxious value" and _ric="some other value";

run;

Into a single call execute do something like:

call execute('Data work.data_longnameconstructhere;');

call execute('set work.if;');

call execute('If date= "some obnoxious value" and _ric="some other value";');

call execute('run;');

You can see in the second bit above that two of the 4 lines don't change so the construction of the "string" variable would be easier.

This bit of your code: strip(put(date,yymmddn8.))||"; inicates that DATE is a SAS date valued numeric.

I think: date="||date||" is doing something unnatural for numerics and _ric="||input(_ric,$13.)||" as a comparison should likely fail as a variable with more than 13 characters will never equal the first 13 characters. And what is wrong with substr?

Re: How to split a large dataset by certain requirements?

ChrisHemedinger — Thu, 20 Oct 2016 20:55:30 GMT

You might also be able to adapt this approach, which doesn't rely on CALL EXECUTE.

Re: How to split a large dataset by certain requirements?

Neal0801 — Thu, 20 Oct 2016 22:08:19 GMT

Thanks. I finally figured out the source of the problem. Since _ric is a characteristic variables, I should put extra ' ' to tell SAS that is characteristic variables.

So my final code is :

_ric='"||input(_ric,$13.)||"'