Create different tables if necessary and stack all if exist

[FADUVIGA]

Hello,

I'm new to programming so I need help, please.

I have a table (TOTAL) that I need to split into small tables with a maximum of 400 rows. After that I need to create new columns in the small tables that were created and finally I need to stack the tables that were created.

Since this table TOTAL can have 200 to 3000 rows, I am using the code bellow to know the numer of lines.

PROC SQL;

CREATE TABLE COUNT AS

SELECT COUNT (*) AS TOTAL_LINES FROM TOTAL;

QUIT;

I am thinking of dividing this number by 400 and see the result, which can range from 7.5 to 0.

So according to this result, generate a specific number of small tables.

Like IF TOTAL_LINES >7 THEN create 8 small tables;

                      Table_1 from TOTAL(firstobs=1 OBS=400);

                      Table_2 from TOTAL (firstobs=401 OBS=800);

                      Table_3 from TOTAL (firstobs=801 OBS=1200);

                      ...

        IF TOTAL_LINES >6 THEN create 7 small tables;

...

        IF TOTAL_LINES <= 1 THEN just crate 1 table : small_table = TOTAL

But how can I create a table based on the result of an IF CLAUSE ?

(I am using SAS 9.4 Enterprise Guide 7.13)

Thank you in advance!!

At the end of all, I need to create a table like this:

Like this: (imagine that it is a tabe)

TYPE DATE         CIA        XXXX     VALUE
1       29/07/2019 BR350 RESULT 500,00
2                                      RESULT - 650,78
2                                       RESULT 400,50
2                                       RESULT 700,50
2                                       (-RESULT) - 500,00
2                                       (-RESULT) 650,78
2                                       (-RESULT) - 400,50
2                                       (-RESULT) - 700,50
1      29/07/2019 BR700 RESULT 650,00
2                                       RESULT 700,00
2                                       RESULT 3.555,00
2                                       RESULT 90,00
2                                       (-RESULT) - 650,00
2                                       (-RESULT) - 700,00
2                                       (-RESULT) - 3.555,00
2                                       (-RESULT) - 90,00

I know how to do it but not if my TOTAL (=RESULT) is longer than 400 lines.

[andreas_lds]

Why do you need to split the dataset? 3k rows is so small, it should be possible to do anything you have to do with that dataset.

The splitting requires either macro-code or using call execute in a data-step. Well, a third approach comes to my mind: using a data-null-step to write a sas-program that is executed using include. Fancy stuff? Yes, of course and definitely something I would not expect from someone at the very beginning of learning a language.

I will post some code later, trains are to crowed to stay concentrated.

[FADUVIGA]

Hi, I need to split the dataset because each small table needs to generate another iqual table with opposite values ​​to cancel the values. Then I need to stack these tables and put specific information only in the first row. Like This:

[Reeza]

Sorry, I can't open attachments. 

Assuming you need to just add a negative value that could be done via an OUTPUT statement.

---

@FADUVIGA wrote:

Hi, I need to split the dataset because each small table needs to generate another iqual table with opposite values ​​to cancel the values. Then I need to stack these tables and put specific information only in the first row. Like This:

---

[FADUVIGA]

Like this: (imagine that it is a tabe)

TYPE DATE         CIA        XXXX     VALUE
1       29/07/2019 BR350 RESULT 500,00
2                                      RESULT - 650,78
2                                       RESULT 400,50
2                                       RESULT 700,50
2                                       (-RESULT) - 500,00
2                                       (-RESULT) 650,78
2                                       (-RESULT) - 400,50
2                                       (-RESULT) - 700,50
1      29/07/2019 BR700 RESULT 650,00
2                                       RESULT 700,00
2                                       RESULT 3.555,00
2                                       RESULT 90,00
2                                       (-RESULT) - 650,00
2                                       (-RESULT) - 700,00
2                                       (-RESULT) - 3.555,00
2                                       (-RESULT) - 90,00

[Reeza]

I'm having a very hard time following this thread and understanding what you're trying to do and which parts you need help with, so I'll bow out from here. 

If you're looking to split the file into specific sizes, I have a macro that does that here:

/*This macro splits a data set into data sets of size N. 
The parameters required are:
1. DSN = input data set name, such as sashelp.cars. 
   The libname should be included unless the data set
   is in the work library.
2. Size = Number of records to be included in each data 
   set. Note that the last data set will be truncated, 
   ie if only 28 records are available only 28 will be 
   written to the output data set.
3. outDsnPrefix = Name of output data sets, will be indexed as 
      outDSNPrefix1
      outDSNPrefix2
      outDSNPrefix3
*/

%macro split (dsn=, size=, outDsnPrefix=Split);

    %*Get number of records and calculate the number of files needed;
    data _null_;
        set &dsn. nobs=_nobs;
        call symputx('nrecs', _nobs);
        n_files=ceil(_nobs/ &size.);
        call symputx('nfiles', n_files);
        stop;
    run;

    %*Set the start and end of data set to get first data set;
    %let first=1;
    %let last=&size.;
    
    %*Loop to split files;
    %do i=1 %to &nfiles;
    
        %*Split file by number of records;
        data &outDsnPrefix.&i.;
            set &dsn. (firstobs=&first obs=&last);
        run;

        %*Increment counters to have correct first/last;
        %let first = %eval(&last+1);
        %let last = %eval((&i. + 1)*&size.);
    %end;
%mend split;

*Example call;
*After running this, you should find 9 data sets named Split1-Split9;
%split(dsn=sashelp.cars, size=50, outDsnPrefix=Split);

https://gist.github.com/statgeek/abc3c6ce1dbeedb84fe7f11da0603cda

---

@FADUVIGA wrote:

Like this: (imagine that it is a tabe)

TYPE DATE         CIA        XXXX     VALUE
1       29/07/2019 BR350 RESULT 500,00
2                                      RESULT - 650,78
2                                       RESULT 400,50
2                                       RESULT 700,50
2                                       (-RESULT) - 500,00
2                                       (-RESULT) 650,78
2                                       (-RESULT) - 400,50
2                                       (-RESULT) - 700,50
1      29/07/2019 BR700 RESULT 650,00
2                                       RESULT 700,00
2                                       RESULT 3.555,00
2                                       RESULT 90,00
2                                       (-RESULT) - 650,00
2                                       (-RESULT) - 700,00
2                                       (-RESULT) - 3.555,00
2                                       (-RESULT) - 90,00

---

[FADUVIGA]

I already know to make this layout that I want but if my RESULT data is more than 400 lines I neet to split it like I dit in the .pdf

[Reeza]

Sounds like something that should be using BY group processing instead.