BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Michaelcwang2
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
  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

7 REPLIES 7
Kurt_Bremser
Super User

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.

Michaelcwang2
Obsidian | Level 7
I'll check and update, thank you.
PaigeMiller
Diamond | Level 26

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
Michaelcwang2
Obsidian | Level 7
Thank you for your comment.
Reeza
Super User
  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 

Michaelcwang2
Obsidian | Level 7
Thank you and I'll study and see how it works.
Michaelcwang2
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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