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

I have a multiple datasets with many columns. I am attempting to go through each one and order the columns and add in any columns stored in a macro variable that aren't in the current table. See below for example.

 

Here is what I have.

 

data have;
input var7 4. var3 4. var2 4. var5 4. var1 4.;
datalines;
492 234 230 921 394
843 230 102 748 239
;

Here is what I want.

 data want;
 input var1 4. var2 4. var3 4. var4 4. var5 4. var6 4. var7 4.;
 datalines;
394 230 234     921     492
239 102 230     748     843
 ;

I have the names of the columns stored in a macro variable that is structured like below.

proc sql;
    select name
    into :my_column_order separated by " "
    from DICTIONARY.COLUMNS
    where UPCASE(LIBNAME) = "WORK"
    and UPCASE(MEMNAME) = "WANT"
    order by name;
quit;

%put &my_column_order;

Is there a way to use my have dataset and &my_column_order to add in and correctly order missing columns and format them the same as the other columns?

 

1 ACCEPTED SOLUTION

Accepted Solutions
maguiremq
SAS Super FREQ
data have;
input var7 4. var3 4. var2 4. var5 4. var1 4.;
datalines;
492 234 230 921 394
843 230 102 748 239
;

 data want;
 input var1 4. var2 4. var3 4. var4 4. var5 4. var6 4. var7 4.;
 datalines;
394 230 234     921     492
239 102 230     748     843
 ;

proc sql;
    select name
    into :my_column_order separated by " "
    from DICTIONARY.COLUMNS
    where UPCASE(LIBNAME) = "WORK"
    and UPCASE(MEMNAME) = "WANT"
    order by name;
quit;

%put &my_column_order;

data want_final;
	retain &my_column_order.;
	set have;
	array vars [*] 4. &my_column_order.;
run;

 

maguiremq_0-1649255911236.png

 

View solution in original post

13 REPLIES 13
maguiremq
SAS Super FREQ
data have;
input var7 4. var3 4. var2 4. var5 4. var1 4.;
datalines;
492 234 230 921 394
843 230 102 748 239
;

 data want;
 input var1 4. var2 4. var3 4. var4 4. var5 4. var6 4. var7 4.;
 datalines;
394 230 234     921     492
239 102 230     748     843
 ;

proc sql;
    select name
    into :my_column_order separated by " "
    from DICTIONARY.COLUMNS
    where UPCASE(LIBNAME) = "WORK"
    and UPCASE(MEMNAME) = "WANT"
    order by name;
quit;

%put &my_column_order;

data want_final;
	retain &my_column_order.;
	set have;
	array vars [*] 4. &my_column_order.;
run;

 

maguiremq_0-1649255911236.png

 

ballardw
Super User

Your example implies that the variables are all numeric. Is this in fact the case? If not you may have quite a bit more work, especially if any of the variables are of different data types in different data sets with the same name. Since you did not pull the FORMAT information from that Have data set, I don't think so.

 

And why? If you intend to combine the data then in a data step or Proc append the order of the variables in the first data set listed will be the resulting order and sets the format and length of the variables.

 

PaigeMiller
Diamond | Level 26

Is a macro variable really needed here? I don't think so:

 

data have;
input var7 4. var3 4. var2 4. var5 4. var1 4.;
datalines;
492 234 230 921 394
843 230 102 748 239
;

data have0;
    input var1-var7;
run;

data want;
    set have0 have;
run;

 

 

--
Paige Miller
yabwon
Onyx | Level 15

Or even:

data have;
input var7 4. var3 4. var2 4. var5 4. var1 4.;
datalines;
492 234 230 921 394
843 230 102 748 239
;

data want;
    array v var1-var7;
    set have;
run;

 

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



A_SAS_Man
Pyrite | Level 9

I don't have a data set with a list of all the variables. The macro variable pulling from want was to demonstrate what the variable looks like. I.e., I don't have my want data set and I can't list all my variables out.

A_SAS_Man
Pyrite | Level 9
this does not produce the output "want" for me.
Kurt_Bremser
Super User

We need to use the array method to create the additional variables:

data want;
array x{*} &my_column_order.;
set have;
run;

But this will make all variables numeric. If you also have character variables, this will fail.

So we need to know more about your real issue, and the real resources you have available.

Post the code that creates the macro variable, and/or post the macro variable itself in a code box (</>).

 

ballardw
Super User

@A_SAS_Man wrote:

I don't have a data set with a list of all the variables. The macro variable pulling from want was to demonstrate what the variable looks like. I.e., I don't have my want data set and I can't list all my variables out.


Providing code/information that is not actually available, such as your Proc SQL obfuscates the entire problem.

 

You specifically included Formats in your request. So, where do you create this macro variable? And where do you want to assign formats?

 

Since you say that you have "multiple data set" perhaps modify that code to use: "and member in ("FIRSTSET" "SECONDSET" "OTHERDATASET") , listing all your actual data set names and Select distinct name. Then you would have list of all variables in all the data sets to play with.

A_SAS_Man
Pyrite | Level 9
I don't have the "want" dataset.
mkeintz
PROC Star

If your task is to re-order variables already in a SAS dataset, then you have lots of working solutions available.

 

But if you are really starting out with raw data that you wish to re-order, then you don't need an extra DATA step, just a minor change in the first DATA step:

 

data want;
  length var1-var7 8;
  input var7 4. var3 4. var2 4. var5 4. var1 4.  ... other vars ... ;
datalines;
....
run;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tom
Super User Tom
Super User

If you have a macro variable with the space delimited names that must exist then use that to generate code that will insure those variable names exist.

For example you might have one of these values for MY_ORDER_VARIABLE.

%let my_order_variable=v1 v2 v3 v3 v4 v5 v6 v7;
%let my_order_variable=v1-v7;
%let my_order_variable=v7 v3 v6-v4 v1 v2;

So that you could use it in a data step like this:

data want;
  retain &my_order_variable;
  set have;
run;

Now if you need some of those variables to be of type CHAR instead of type NUM then you need more information than just the names of the variables.  Specifically you need to know the LENGTH.  So if for example V6 should be a character variable that can hold up to 20 bytes and the other 6 variables are numeric you might make the macro variable hold something like this:

%let my_order_variable=v1 v2 v3 v3 v4 v5 8 v6 $20 v7 8;

Which you could then use with a LENGTH statement to define the variables and their order.

data want;
  length &my_order_variable;
  set have;
run;

And if any of the variables require a special format to be attached to them, such as DATE or DATETIME values, then you need even more information than just the variable's type and storage length.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 13 replies
  • 1994 views
  • 5 likes
  • 8 in conversation