DATA Step, Macro, Functions and more

How to keep appending data?

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

How to keep appending data?

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!


Accepted Solutions
Solution
‎08-27-2017 09:20 PM
Super User
Posts: 22,847

Re: How to keep appending data?

Posted in reply to Michaelcwang2
  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 

View solution in original post


All Replies
Super User
Posts: 9,573

Re: How to keep appending data?

[ Edited ]
Posted in reply to Michaelcwang2

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 23

Re: How to keep appending data?

Posted in reply to KurtBremser
I'll check and update, thank you.
Respected Advisor
Posts: 2,649

Re: How to keep appending data?

[ Edited ]
Posted in reply to Michaelcwang2

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.

--
Paige Miller
Contributor
Posts: 23

Re: How to keep appending data?

Posted in reply to PaigeMiller
Thank you for your comment.
Solution
‎08-27-2017 09:20 PM
Super User
Posts: 22,847

Re: How to keep appending data?

Posted in reply to Michaelcwang2
  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 

Contributor
Posts: 23

Re: How to keep appending data?

Thank you and I'll study and see how it works.
Contributor
Posts: 23

Re: How to keep appending data?

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;
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 342 views
  • 2 likes
  • 4 in conversation