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

I have one data set

Like this,

ORDERADDRE:HOME_1TELEPONE:HOME_1TELEPONE:FAX_1ADDRE:HOME_2TELEPONE:HOME_2ADDRE:HOME_3
1A1122B22C
2B2233C33D
3C3344D44E
4D4455E55F
5E5566F66G
6F6677G77H
7G7788H88I
8H8899I99J
9I99110J110K
10J10121K121L

Is there a way to automatically have the columns in the data set order as follows?

ORDERADDRE:HOME_1ADDRE:HOME_2ADDRE:HOME_3TELEPONE:HOME_1TELEPONE:HOME_2TELEPONE:FAX_1
1ABC112222
2BCD223333
3CDE334444
4DEF445555
5EFG556666
6FGH667777
7GHI778888
8HIJ889999
9IJK99110110
10JKL10121121

Thank you for your help!!!!

Allan

1 ACCEPTED SOLUTION

Accepted Solutions
Mit
Calcite | Level 5 Mit
Calcite | Level 5

with your data;

data want;

retain ADDRE:HOME_1 ADDRE:HOME_2 ADDRE:HOME_3 ORDER TELEPONE:FAX_1 TELEPONE:HOME_1 TELEPONE:HOME_2;

set have;

run;

OR

%let var= ADDRE:HOME_1 ADDRE:HOME_2 ADDRE:HOME_3 ORDER TELEPONE:FAX_1 TELEPONE:HOME_1 TELEPONE:HOME_2;

data want;

retain &var.;

set have;

run;

             
ADDRE:HOME_1ADDRE:HOME_2ADDRE:HOME_3ORDERTELEPONE:FAX_1TELEPONE:HOME_1TELEPONE:HOME_2

View solution in original post

5 REPLIES 5
Mit
Calcite | Level 5 Mit
Calcite | Level 5

The easiest way is:

export the variables to Excel and then sort it . Then run the following code

%let var= <copy and paste the sorted variable names from Excel here>;

data want;

     retain &var.;

set have;

run;

Mit
Calcite | Level 5 Mit
Calcite | Level 5

with your data;

data want;

retain ADDRE:HOME_1 ADDRE:HOME_2 ADDRE:HOME_3 ORDER TELEPONE:FAX_1 TELEPONE:HOME_1 TELEPONE:HOME_2;

set have;

run;

OR

%let var= ADDRE:HOME_1 ADDRE:HOME_2 ADDRE:HOME_3 ORDER TELEPONE:FAX_1 TELEPONE:HOME_1 TELEPONE:HOME_2;

data want;

retain &var.;

set have;

run;

             
ADDRE:HOME_1ADDRE:HOME_2ADDRE:HOME_3ORDERTELEPONE:FAX_1TELEPONE:HOME_1TELEPONE:HOME_2
amcacr
Calcite | Level 5

Thank you,

But I have a problem with that solution, in some cases the number of columns it would be diferent, not always are the same number of columns. Thats my problem.

PaigeMiller
Diamond | Level 26

Untested code

proc contents data=have out=_cont_ noprint;

run;

proc sql;

     select distinct name into :var separated by ' ' from _cont_ where lowcase(name)^='order' order by name;

quit;

data want;

     retain order &var;

     set have;

run;

Which brings up the interesting question ... why do this? None of the procedures used by SAS care what order the columns are in; and if you are doing this for presentation, then procedures such as PROC PRINT and PROC REPORT let you explicity state what order the variables are to appear in.

--
Paige Miller
amcacr
Calcite | Level 5

Thanks.

Works perfectly!!!!

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
  • 5 replies
  • 1109 views
  • 5 likes
  • 3 in conversation