topic Re: Problem to sum all the info in New SAS User

Problem to sum all the info

Yona22 — Wed, 18 Mar 2020 07:31:30 GMT

Re: Problem to sum all the info

PaigeMiller — Sun, 15 Mar 2020 17:41:18 GMT

You need to combine the data into a single data set. This is either a MERGE or SET in a DATA step, or a JOIN in PROC SQL. Since we don't have your data, you will have to do this part.

Then to do the summing, you want PROC SUMMARY. Example:

proc summary nway data=have;
    class name;
    var payment;
    output out=sums sum=sum_payment;
run;

Re: Problem to sum all the info

Kurt_Bremser — Sun, 15 Mar 2020 19:16:03 GMT

If you need to keep the separate datasets, create a view:

data dataview/view=dataview;
set
  data1201
  data1202
  data1203
  ......
;
run;

and then run proc summary over that.

Re: Problem to sum all the info

Yona22 — Wed, 18 Mar 2020 07:09:59 GMT

Re: Problem to sum all the info

Yona22 — Wed, 18 Mar 2020 07:02:06 GMT

Re: Problem to sum all the info

Patrick — Mon, 16 Mar 2020 05:24:38 GMT

Remove the semicolons between the table names. The syntax needs to be a SET statement with a list of tables name and then a single semicolon which terminates the statement.

If code has errors then also please always the SAS log which shows the actual error message.

Re: Problem to sum all the info

Yona22 — Wed, 18 Mar 2020 07:11:00 GMT

Re: Problem to sum all the info

Kurt_Bremser — Mon, 16 Mar 2020 06:38:34 GMT

Use call execute() in a data step to create the actual data step:

data _null_;
period = '01mar2002'd;
call execute('data want; set');
do until (period > '01dec2019'd);
  call execute(' data' !! substr(put(year(period),z4.),3,2) !! put(month(period),z2.));
  period = intnx('month',period,1,'b');
end;
call execute(';run;');
run;

How to loop all the file and combine into 1 data

Yona22 — Wed, 18 Mar 2020 07:02:50 GMT

Re: How to loop all the file and combine into 1 data

PeterClemmensen — Mon, 16 Mar 2020 07:37:32 GMT

If all of the desired input data sets start with "tab", you can do

data comp;
    set tab:;
run;

Re: How to loop all the file and combine into 1 data

Yona22 — Wed, 18 Mar 2020 07:04:12 GMT

Re: How to loop all the file and combine into 1 data

Patrick — Mon, 16 Mar 2020 08:06:39 GMT

Please don't ask the same question twice. This only messes up discussions. @Kurt_Bremser has already given you a working answer here.

Re: How to loop all the file and combine into 1 data

Kurt_Bremser — Mon, 16 Mar 2020 08:17:23 GMT

I merged this back into the original question.

You can use the method I posted with any prefix, and any dataset with an additional suffix will be excluded.

e

Yona22 — Wed, 18 Mar 2020 15:31:36 GMT

Re: Problem to sum all the info

Kurt_Bremser — Mon, 16 Mar 2020 10:04:03 GMT

@Yona22 wrote:
Hi, I used the code but just listed out all the dataset. May I know how can I put all of them together in a new data?

Did you run my call execute code? It will automatically pick up all datasets in the range that follow the naming pattern.

Re: How to loop all the file and combine into 1 data

ballardw — Mon, 16 Mar 2020 14:53:56 GMT

@Yona22 wrote:
Hi, I have lots of dataset and named with year and months (TAB0203, TAB0204,..... until TAB1912). I want to extract all of them. This is my code

DATA COMB;
SET TAB0203
TAB0204
....
TAB1912;

RUN;

But it only read the 3 dataset and didn't read the rest of the data from TAB0205 until TAB1911. May I know how should I write for the code in order to read all of them?

AS was suggested before: COPY the LOG and paste the copied text into a code box opened on the Forum with the {I} or "running man" icon.

Re: Problem to sum all the info

Yona22 — Wed, 18 Mar 2020 07:29:25 GMT

Re: Problem to sum all the info

Patrick — Tue, 17 Mar 2020 03:27:58 GMT

@Yona22

Below fully working and tested code using your table names.

/* create sample source tables */
%macro doit();
  data _null_;
    length source_dslist $2000;
    do year=0 to 20;
      do month=1 to 12;
        source_dslist=catx(' ',source_dslist, cats('tab', put(year,z2.),put(month,z2.)));
       end;
    end;
    call symputx('source_dslist', source_dslist);
    stop;
  run;
%mend;
%doit();
data &source_dslist tab0203_copy tab0215;
  set sashelp.class(obs=2);
run;

/* create list of source tables within desired date range.
   and store result in macro variable &dslist
*/
%let dslist=;
proc sql noprint;
  select cats(libname,'.',memname) into :dslist separated by ' '
  from dictionary.tables
  where libname='WORK' and substr(memname,1,3)='TAB'
    and input(compress(memname,,'kd'),yymmn4.) between '01mar2002'd and '01dec2019'd
  order by memname
  ;
quit;
%put &=dslist;

/* combine all source tables */
data want;
  length _inds $41;
  format payment_date yymmn4.;
  set &dslist indsname=_inds;
  payment_date=input(compress(_inds,,'kd'),yymmn4.);
run;

proc print data=want(obs=2);
run;