BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Rabelais
Fluorite | Level 6

Starting from this table

Rabelais_0-1708424366781.png

I'd like to obtain this other table (numeric part multiplied by 2) using proc iml only and without explicitly using the column names but rather using only variables created inside proc iml

Rabelais_1-1708424421077.png

 

The following code

  1. creates the table
  2. reads numeric and character subsets in proc iml
  3. multiplies by 2 the numeric subset
  4. exports the numeric subset by setting the same row names as the original table and the same column names as the numeric part of the original table

 

data HAVE;
country = 'laos'; value = 18; h = 1; output;
country = 'mira'; value = 21; h = 3; output;
run;

proc iml;
use HAVE;
read all var _NUM_  into num_mtx[colname=num_names];
read all var _CHAR_ into char_mtx[colname=char_names];
close;

num_mtx = num_mtx*2;

create WANT from num_mtx[colname=num_names rowname=char_mtx];
append from num_mtx[rowname=char_mtx];
close;
quit;

 

And this is the output

Rabelais_2-1708425440374.png

As you can see there is a problem: the name of the column containing the row names is char_mtx instead of country.

 

The problem could be easily solved by explicitly renaming the column during the export

create WANT(rename=(char_mtx=country)) from ...

but since I have to automize all the process, I cannot write the explicit column name (i.e. country) in the code.

 

Running

print char_names;

I noticed that char_names correctly contains the char column name

Rabelais_3-1708426077361.png

however I'm not able to use it in order to rename the column containing the row names. I tried several commands such as 

 

create WANT(rename=(char_mtx=char_names)) from ...

and

%let name = char_names;
create WANT(rename=(char_mtx=&name)) from ...

but none of them works.

I also tried to create a new vector containing both the character and numeric column names, then setting the colname equal to that vector, but only the numeric subset columns are renamed as you can see below

all_names = char_names || num_names;
create WANT from num_mtx[colname=all_names rowname=char_mtx];

Rabelais_5-1708427026472.png

 

Any suggestion? I'm sure the problem is a piece of cake

 
 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Rick_SAS
SAS Super FREQ

You don't state what version of SAS/IML you are using, but in modern versions you can place multiple matrices on the CREATE and APPEND statements and specify the column names for all columns. Your problem is solved in the article, "Write numeric and character matrices to a data set from SAS/IML".

 

For your example, the CREATE and APPEND statements would look like this:

 

create WANT from char_mtx num_mtx[colname=(char_names || num_names)];
append from char_mtx num_mtx;
close;

View solution in original post

5 REPLIES 5
Rick_SAS
SAS Super FREQ

You don't state what version of SAS/IML you are using, but in modern versions you can place multiple matrices on the CREATE and APPEND statements and specify the column names for all columns. Your problem is solved in the article, "Write numeric and character matrices to a data set from SAS/IML".

 

For your example, the CREATE and APPEND statements would look like this:

 

create WANT from char_mtx num_mtx[colname=(char_names || num_names)];
append from char_mtx num_mtx;
close;
Rabelais
Fluorite | Level 6

Thank you very much!

 

Do you also know if it is possible for the matrices to inherit the formats of the input table? For example in this code

 

data HAVE;
format country $12. value 18.2 h 18.1;
country = 'laos'; value = 18.12345; h = 1.08; output;
country = 'mira'; value = 21.98765; h = 3.12; output;
run;

proc iml;
use HAVE;
read all var _NUM_  into num_mtx[colname=num_names];
read all var _CHAR_ into char_mtx[colname=char_names];
close;

create WANT from char_mtx num_mtx[colname=(char_names || num_names)];
append from char_mtx num_mtx;
close;
quit;

 

 

the data step creates the table HAVE with these attributes

Rabelais_0-1708441938056.png

 

and the proc iml takes HAVE as input and creates the table WANT with these attributes

Rabelais_1-1708442008231.png

 

and as you can see the lengths match, while the formats are empty. With these commands

mattrib char_mtx format=$12.;
mattrib num_mtx format=18.2;

we can manually change the formats, but is there a command to automatically inherit the formats of the input table HAVE?

 

In the case such a command doesn't exist, then how to format separately the two numeric variables "value" and "h"?

 

If necessary I could start a new topic about this. Thank you for all

 
 

 

 

Rick_SAS
SAS Super FREQ

And if we answer this new question, the next questions are,  "What about lengths?", "What about labels?", and "What about <other attributes>?"

 

In your original post, you said "using proc iml only ...", but that's not the best way to handle these issues. There are better ways to accomplish your goals if you also leverage the DATA step, catalogs, and Base SAS procedures.

 

For this problem, my first comment is that you don't attempt to read character variables into IML and then write them out again. Read in the numeric variables, modify them, and then merge the original character variables and the modified numeric variables:

 

/* combine the original char vars and the new numeric vars */
data CharAndNumVars;        
merge Have(keep=_CHARACTER_) /* retain character variables unchanged */
      Want;                  /* the modified numerical variables */
run;

If you want the new data to retain the original variable attributes, then use Base SAS techniques to do that. For example, the following DATA step sets all variable attributes from the original data, then appends the modified values:

data NewWant;
set Have(in=orig)          /* create all variable attributes */
    CharAndNumVars;
if ^orig;
run;

/* show the variable attributes and the values */
proc contents data=NewWant; run;
proc print data=NewWant; run;
Rabelais
Fluorite | Level 6
It works like a charm! I'm going to study your code to understand how it works.

Thank you very much for all the suggestions and for sharing your valueable knowledge
Rabelais
Fluorite | Level 6

I guess that this

data NewWant;
set Have
CharAndNumVars(in=new);
if new;
run;

does the same job, doesn't it?

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 5 replies
  • 604 views
  • 6 likes
  • 2 in conversation