topic Re: How to keep appending data? in SAS Programming

How to keep appending data?

Michaelcwang2 — Fri, 25 Aug 2017 09:20:04 GMT

Dear all,

I'm trying to append each calculated descriptive statistics in an iteration to a dataset. My coding is as below:

%MACRO SPCDIST;  /*Macro file is needed for repetitive SQL statement*/
%do j=1 %to 10;
   PROC SQL;
   CREATE TABLE work.query AS
   SELECT j , i , X , Y FROM MYSECOND.SPC 
   WHERE J=&j AND Y=0;
   RUN;
   QUIT;

   /*PROC DATASETS NOLIST NODETAILS;
   CONTENTS DATA=work.query OUT=WORK.details;
   RUN;  

   PROC PRINT DATA=WORK.details;
   RUN;*/    /*No need to print work.detail*/
   ods noproctitle;
   ods graphics / imagemap=on;

   proc means data=work.query chartype mean std min max n vardef=df skew SKEWNESS KURT KURTOSIS;
	var X;
	output out=work.skewtemp skew=Distskew KURT=DISKURT;
	
   run;
   data skewdata;
   set skewtemp;
   /*MODIFY skewdata ;
   /*SKEWSQUARE=Distskew*Distskew;*/
   /*output;*/
   run;   /*How to "amend" the file skewdata?*/
   proc append base=work.skewdata data=work.skewtemp force;

   run;

   proc print data=work.skewdata;
   run;

   run;
   ods graphics / reset imagemap;

   /*--SGPLOT proc statement--*/
   proc sgplot data=WORK.QUERY;
	/*--Histogram settings--*/
	histogram X /;

	/*--Vertical or Response Axis--*/
	yaxis grid;
   run;

   ods graphics / reset;
%end;
%MEND;
%SPCDIST;

I expect to have 10 observations recorded in the work.skewdata dataset but there is always two items for each iteration, would somebody help to clarify where went wrong and how to solve.

Thank you!

Re: How to keep appending data?

Kurt_Bremser — Fri, 25 Aug 2017 09:28:17 GMT

This part

   data skewdata;
   set skewtemp;
   /*MODIFY skewdata ;
   /*SKEWSQUARE=Distskew*Distskew;*/
   /*output;*/
   run;   /*How to "amend" the file skewdata?*/
   proc append base=work.skewdata data=work.skewtemp force;

   run;

creates skewdata once with the contents of skewtemp, and then appends skewtemp to it, so you get twice the contents of skewtemp.

Since skewdata is initialized by the first of the two datasteps in each iteration, only the doubled results of the last iteration will end up in skewdata.

Initialize skewdata once before the iterations (or remove it altogether), and only keep the proc append step.

Re: How to keep appending data?

PaigeMiller — Fri, 25 Aug 2017 11:01:42 GMT

How about extracting all of the data from the database in one PROC SQL extract, and then doing PROC MEANS and PROC SGPLOT with a BY statement, and then you don't need all this looping and you don't need PROC APPEND either.

Re: How to keep appending data?

Reeza — Fri, 25 Aug 2017 15:18:06 GMT

  proc means data=work.query chartype NWAY mean std min max n vardef=df skew SKEWNESS KURT KURTOSIS;
       where (j between 0 and 10) and Y=0;
       class J;
	var X;
	output out=work.skewtemp skew=Distskew KURT=DISKURT;
	
   run;

You don't need a macro here. See the code above and note the following changes:

NWAY

WHERE

CLASS

Re: How to keep appending data?

Michaelcwang2 — Sat, 26 Aug 2017 00:11:00 GMT

I'll check and update, thank you.

Re: How to keep appending data?

Michaelcwang2 — Sat, 26 Aug 2017 00:11:43 GMT

Thank you for your comment.

Re: How to keep appending data?

Michaelcwang2 — Sat, 26 Aug 2017 00:12:45 GMT

Thank you and I'll study and see how it works.

Re: How to keep appending data?

Michaelcwang2 — Mon, 28 Aug 2017 01:20:27 GMT

Hi Reeza,

I modify the code as you comment below: It works and almost there as oputput of skewdata includes observation 13 to 22 which looks redundant. I'll try to fix it and really appreciate your support!

PROC SQL;
   CREATE TABLE work.query AS
   SELECT j , i , X , Y FROM MYSECOND.SPC; 
   /*WHERE J=&j AND Y=0;*/
   RUN;
   QUIT;

   /*PROC DATASETS NOLIST NODETAILS;
   CONTENTS DATA=work.query OUT=WORK.details;
   RUN;  

   PROC PRINT DATA=WORK.details;
   RUN;*/    /*No need to print work.detail*/
   ods noproctitle;
   ods graphics / imagemap=on;

   proc means data=work.query chartype NWAY mean std min max n vardef=df skew SKEWNESS KURT KURTOSIS;
	var X;
	output out=work.skewtemp skew=Distskew KURT=DISKURT ;where (j between 0 and 10) and Y=0;       class J;
	
   run;
   data skewdata;
   set skewtemp;
   /*MODIFY skewdata;*/
   SKEWSQUARE=Distskew*Distskew;
   Excess_Kurt=DISKURT+3;
   /*output;*/
   run;   /*How to "amend" the file skewdata?*/
   proc append base=work.skewdata data=work.skewtemp force;

   run;

   proc print data=work.skewdata;
   run;

   run;
   ods graphics / reset imagemap;

   /*--SGPLOT proc statement--*/
   proc sgplot data=WORK.QUERY;
	/*--Histogram settings--*/
	histogram X /;

	/*--Vertical or Response Axis--*/
	yaxis grid;
   run;

   ods graphics / reset;