Help using Base SAS procedures

Assigning Variable Order in PROC EXPORT Statement

Reply
New Contributor
Posts: 2

Assigning Variable Order in PROC EXPORT Statement

[ Edited ]

I have the PROC EXPORT statement with a KEEP statement in the DATA step, I thought, would simultaneously keep the variables from my data set I want to export and retain them in this order. However, when I run this statement, the variables are not in this order. How do I assign variable order in my EXPORT statement so I don't have to manually reorder them in excel?

 

Thanks!

 

PROC EXPORT 
DATA=Work.Surveytempdata
(keep=Response_ID Response_Status Region_ AnotherOffer Job Q11 Q12 Length_Employment Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21 Q22 Q23 Q24 TrainingYN Q26 Q27 Q28 Q29 Q30 Q31 Q32 TrainingOther Q33 TrainingTimeframe Q35 TrainingPreparedMe
Q37 Q38 Q39 Q40 Q41 Q42	ChangeTrainingOther WorkloadExpected WorkloadNotExpected Q46 PhysicallyDemanding TooPhysicallyDemanding MostPhysicallyDemanding Q50 WorkingConditions
Q52 Q53 Q54 Q55 WorkingConditionsOther Q56 InfoForJob Q58 Q59 Q60 Q61 InfoForJobOther Q62 ToolsResources Q64 Q65 ToolsResourcesOther Q66 SkillsAbilities Q68
SupervisorCommunication Q70 DignityRespect Q72 WorkAgain Q74 Q75)
OUTFILE='\\SharedDrive\Desktop\survey'
DBMS=xlsx;
label;
run;
Respected Advisor
Posts: 3,777

Re: Assigning Variable Order in PROC EXPORT Statement

[ Edited ]

Use a view.

 

%let keep=Response_ID Response_Status Region_ AnotherOffer Job Q11 Q12 Length_Employment Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21 Q22 Q23 Q24 TrainingYN Q26 Q27 Q28 Q29 Q30 Q31 Q32 TrainingOther Q33 TrainingTimeframe Q35 TrainingPreparedMe
Q37 Q38 Q39 Q40 Q41 Q42	ChangeTrainingOther WorkloadExpected WorkloadNotExpected Q46 PhysicallyDemanding TooPhysicallyDemanding MostPhysicallyDemanding Q50 WorkingConditions
Q52 Q53 Q54 Q55 WorkingConditionsOther Q56 InfoForJob Q58 Q59 Q60 Q61 InfoForJobOther Q62 ToolsResources Q64 Q65 ToolsResourcesOther Q66 SkillsAbilities Q68
SupervisorCommunication Q70 DignityRespect Q72 WorkAgain Q74 Q75;

data surveyV/view=surveyV;
   retain &keep;
   set surveytemp(keep=&keep);
   run;


PROC EXPORT DATA=surveyv OUTFILE='\\SharedDrive\Desktop\survey' DBMS=xlsx;
   label;
   run;

 

New Contributor
Posts: 2

Re: Assigning Variable Order in PROC EXPORT Statement

Hi @data_null__ thanks for your help.

 

If I understand you correctly, you've written a let statement that creates a variable called keep which identifies which variables I want to keep. Then in a data statement, you're creating a variable that invokes keep and retains the variables set in that statement above. Then you are exporting the view with the labels. Is this correct?

 

I have two question:

- Why do you have "keep=" written twice? I've never seen that before.

- When I run the code, it tells me that the view does not exist. Is it because I've created a view from a data set with the same name in the data step?:  

ERROR: File WORK.SURVEYV.DATA does not exist.
Super User
Posts: 6,963

Re: Assigning Variable Order in PROC EXPORT Statement

The keep=keep= is a typo. Just use %let keep=var_list ;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 6,963

Re: Assigning Variable Order in PROC EXPORT Statement

PROC EXPORT examines the dataset and the creates a DATA step that does the real work (you see this in the log)

Any DATA step retains the order of variables as it is in the input dataset, unless you use the variable names before the SET statement in another statement.

eg

 

DATA want;

FORMAT

  var2

  var1

  var7

  var3

;

SET have;

......

RUN;

 

will change the order of the variables so those in the (otherwise empty) FORMAT statement appear first.

 

Your possible solutions are:

reorder the variables as described with a DATA step;

reorder the variables with a CREATE TABLE AS SELECT in PROC SQL

use the code created by PROC EXPORT in a newly created Code window and change the order of variables there

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 4 replies
  • 2264 views
  • 2 likes
  • 3 in conversation