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

Hello,

 

I have one large data set (P_Top) that I divide into smaller datasets based on variable POD. These smaller data sets are then sent to a directory as excel files. My problem is that I want to drop the POD variable from these data sets prior to exporting them but am running into issues doing that. I'm pretty much blanking out because I'm using POD as the variable in the macro to create them to begin with. I feel like I'm tripping over my self here. 

 

Appreciate any thoughts? Thanks. 

 

options mlogic mprint;
%macro split1 (data=, var=);

/*distinct field */
proc sort data = P_Top out = PDS nodupkey;
by POD;
run;

/* workbook variables */
data PDS;
set PDS;
POD1 = tranwrd(trim(POD),' ','_');
keep POD POD1;
run;

data _null_;
set PDS end = last;
call symputx ('val'||left(_n_),POD);
call symputx ('valx'||left(_n_),POD1);
if last then call symput('count',_n_);
run;


/* POD datasets */
data %do i = 1 %to &count;
&&valx&i %end;
;

set &data;
select(&var);
%do i = 1 %to &count;
when ("&&val&i") output &&valx&i;
%end;
otherwise;
end;
run;


%do i = 1 %to &count;
proc export
data = &&valx&i
outfile = "\\XD00040\&&valx&i"
DBMS = EXCEL replace;
SHEET = "&&valx&i";
run;
%end;

%mend split1;

%split1
(
data = P_Top,
var = POD
);

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

There is a spot in the macro where you create the data sets.  That would be the proper point to drop the variable:

 

data %do i=1 %to &count;

            &&valx&i (drop=pod)

        %end;

         ;

View solution in original post

3 REPLIES 3
Astounding
PROC Star

There is a spot in the macro where you create the data sets.  That would be the proper point to drop the variable:

 

data %do i=1 %to &count;

            &&valx&i (drop=pod)

        %end;

         ;

Ody
Quartz | Level 8 Ody
Quartz | Level 8
I tried that so many different ways but the correct way. I didnt realize the correct syntax was to include the 'drop =' in parentheses.

Thanks for the help!



ballardw
Super User

The drop could have been in several places. The data set option is one: data outputdatasetname (drop = variablelist) exclude the variables only from the specified set.

 or as a separate statement within the data step:   drop pod; run;  or: Set &data; drop pod;

Poor form but it would even work within a Select block as Drop like Format and label are not actually executeable statements.

would drop the selected variables from ANY data set built in the data step. Note this form does not use "="

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
  • 3 replies
  • 769 views
  • 0 likes
  • 3 in conversation