topic Re: Assigning Variable Order in PROC EXPORT Statement in SAS Procedures

Assigning Variable Order in PROC EXPORT Statement

CarpeDatum — Fri, 11 Sep 2015 21:26:05 GMT

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;

Re: Assigning Variable Order in PROC EXPORT Statement

data_null__ — Mon, 14 Sep 2015 15:46:25 GMT

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;

Re: Assigning Variable Order in PROC EXPORT Statement

Kurt_Bremser — Mon, 14 Sep 2015 10:55:50 GMT

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.

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

Re: Assigning Variable Order in PROC EXPORT Statement

CarpeDatum — Mon, 14 Sep 2015 13:22:43 GMT

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.

Re: Assigning Variable Order in PROC EXPORT Statement

Kurt_Bremser — Mon, 14 Sep 2015 14:03:55 GMT

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

Re: Assigning Variable Order in PROC EXPORT Statement

YHJ — Sun, 28 Jan 2018 09:29:59 GMT

have you got the order you wanted? can you tell me how you have done?

Re: Assigning Variable Order in PROC EXPORT Statement

Kurt_Bremser — Sun, 28 Jan 2018 09:56:15 GMT

This thread is from > 2 years back.This means that only the few people that participated will see any new entries, thereby massively reducing the number of people that could help you.

Please post your question in a new thread, with code and data examples.

Re: Assigning Variable Order in PROC EXPORT Statement

YHJ — Mon, 29 Jan 2018 13:44:58 GMT

Many thanks