BookmarkSubscribeRSS Feed
CarpeDatum
Calcite | Level 5

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;
7 REPLIES 7
data_null__
Jade | Level 19

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;

 

CarpeDatum
Calcite | Level 5

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.
Kurt_Bremser
Super User

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

YHJ
Calcite | Level 5 YHJ
Calcite | Level 5

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

Kurt_Bremser
Super User

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.

YHJ
Calcite | Level 5 YHJ
Calcite | Level 5

Many thanks

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 14490 views
  • 2 likes
  • 4 in conversation