turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Sas: Columns multiplication with their correspondi...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-07-2016 11:41 AM

Dear Sir,

I could not find the answers in google. I have a dataset with many columns.

The columns are labeled as

X_height, X_weight, Y_height , Y_weight ...

How do I multiply height with weight for the same pair. ie X_height * X_weight, Y_height * Y_weight.

Any ideas?

Thank you

Leo

Accepted Solutions

Solution

03-08-2016
05:31 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to CheerfulChu

03-07-2016 08:52 PM

If you had told us that you are a MATLAB user, we could have suggested a very simple SAS/IML solution. IML is the SAS Interactive Mtrix Language, which is very close to MATLAB. Also, it looks like the variables that you want multiplied are adjacent to each other, which was not clear from your initial description.

Here is a SAS/IML solution, which might be more to your liking:

```
data Have;
input Date X_height X_weight Y_height Y_weight;
datalines;
1990 100 50 200 40
1991 80 40 300 30
;
proc iml;
use Have(drop=Date);
read all var _NUM_ into M[colname=varNames];
close Have;
odds = do(1, ncol(M), 2); /* odd columns */
evens = odds + 1; /* even columns */
mult = M[ , evens] # M[ , odds]; /* multiply adjacent values */
prefix = substr(varNames[,odds], 1, 7); /* extract 7-character prefix */
names = prefix + "_hw"; /* append comon suffix */
create MultOut from Mult[colname=names];/* output to data set */
append from Mult;
close MultOut;
```

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to CheerfulChu

03-07-2016 12:25 PM

Well, I suppose the first question is why do you have data like that? Normally a normalised - i.e. data down the page rather than across is far easier to work with. If you have to work with a transposed dataset, then you should at least try to make it easy for youself. For example naming the variables:

height1 height2 height3 weight1 weight2 etc.

Then you can simply setup arrays:

data want;

set have;

array weight{3};

array height{3};

array results{3};

do i=1 to 3;

results{i}=height{i} * weight{i};

end;

run;

However again, you would be better off sorting your data out first so you have:

RECORD_ID WEIGHT HEIGHT

1 xxx xxx

2 xxx xxx

3 xxx xxx

It will make your life and your coding far easier.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-07-2016 12:58 PM

Hi Sir,

Very good suggestion.

Well, I suppose the first question is why do you have data like that?

>> I cant simply rename X, Y as 1 and 2 is because X and Y actually represent a 8 character ID. These IDs are needed later in a process to identify them. This dataset has over 1000 unique ID. I thought if I transpose them, I can calculate them easier in a matrix form. But seem like I cant pair them up like X_weight * X_height, Y_weight * Y_height ...

Seem like Sas has a limitation here.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to CheerfulChu

03-07-2016 01:36 PM

Here's a way to restructure your data ... not easy, but not terribly lengthy:

data heights (keep=ID height) weights (keep=ID weight);

set have;

array nums {*} _numeric_;

do _n_=1 to dim(nums);

test_name = upcase(vname(nums{_n_}));

if scan(test_name,2, '_') = 'HEIGHT' then do;

id = scan(test_name, 1, '_');

height = nums{_n_};

output heights;

end;

else if scan(test_name, 2, '_') = 'WEIGHT' then do;

id = scan(test_name, 1, '_');

weight = nums{_n_};

output weights;

end;

end;

run;

proc sort data=heights;

by id;

run;

proc sort data=weights;

by id;

run;

data combined;

merge heights weights;

by id;

product = height * weight;

run;

It's untested code, but should be fine. And I agree that this would be a much better form for storing the data.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

03-07-2016 05:39 PM

It works.

Seem like you are doing transpose.

ie date x_height x_weight y_height y_weight

1990 100 50 80 40

1991 90 45 70 35

to

date ID weight date ID height

1990 X 50 1990 X 100

1991 X 45 1991 X 90

1990 Y 40 1990 Y 80

Then do multiplication. After which I need to transpose back to this format

date x_wh y_wh etc

In matlab,

I can simply do column multiplication ie column 1 * column 2, column 3 * column 4 after I have sorted the column positions.

hmmmmm

anyway thanks for your answer

Solution

03-08-2016
05:31 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to CheerfulChu

03-07-2016 08:52 PM

If you had told us that you are a MATLAB user, we could have suggested a very simple SAS/IML solution. IML is the SAS Interactive Mtrix Language, which is very close to MATLAB. Also, it looks like the variables that you want multiplied are adjacent to each other, which was not clear from your initial description.

Here is a SAS/IML solution, which might be more to your liking:

```
data Have;
input Date X_height X_weight Y_height Y_weight;
datalines;
1990 100 50 200 40
1991 80 40 300 30
;
proc iml;
use Have(drop=Date);
read all var _NUM_ into M[colname=varNames];
close Have;
odds = do(1, ncol(M), 2); /* odd columns */
evens = odds + 1; /* even columns */
mult = M[ , evens] # M[ , odds]; /* multiply adjacent values */
prefix = substr(varNames[,odds], 1, 7); /* extract 7-character prefix */
names = prefix + "_hw"; /* append comon suffix */
create MultOut from Mult[colname=names];/* output to data set */
append from Mult;
close MultOut;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Rick_SAS

03-08-2016 05:25 PM

I keep the date within the proc iml.

proc iml;

use Have;

read all var _NUM_ into M[colname=varNames];

close Have;

evens = do(2, ncol(M), 2);

odds = evens + 1;

mult = M[ , evens] # M[ , odds];

mult =M[, 1] || mult;

prefix = substr(varNames[,odds], 1, 7);

names = prefix + "_hw";

bnames= varNames[1]|| names;

create MultOut from Mult[colname= bnames];

append from Mult;

close MultOut;

Thanks =)

proc iml;

use Have;

read all var _NUM_ into M[colname=varNames];

close Have;

evens = do(2, ncol(M), 2);

odds = evens + 1;

mult = M[ , evens] # M[ , odds];

mult =M[, 1] || mult;

prefix = substr(varNames[,odds], 1, 7);

names = prefix + "_hw";

bnames= varNames[1]|| names;

create MultOut from Mult[colname= bnames];

append from Mult;

close MultOut;

Thanks =)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to CheerfulChu

03-07-2016 12:51 PM

You could also generate the code using macros, or using arrays.

Can you explain your situation a bit more, sample input and desired output is helpful.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

03-07-2016 01:01 PM

Hi Reeza,

Input:

Date X_height X_weight Y_height Y_weight

1990 100 50 200 40

1991 80 40 300 30

Output

Date X_hw Y_hw

1990 5000 8000

1991 3200 9000

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to CheerfulChu

03-07-2016 01:13 PM

I guess I don't understand the problem. Are you trying to generate the names programmatically? If you know the names you want then just use

```
data A;
input Date X_height X_weight Y_height Y_weight;
X_hw = X_Height * X_Weight;
Y_hw = Y_Height * Y_Weight;
datalines;
1990 100 50 200 40
1991 80 40 300 30
;
proc print;
var Date X_hw Y_hw;
run;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Rick_SAS

03-07-2016 01:21 PM

Hi Rick,

I have a large dataset with 1000 pair of columns:

For simplicity,

Input

Date X_height X_weight Y_height Y_weight Z_height Z_weight etc

Output:

Date X_hw Y_hw X Z_hw etc

So there would be 1000 variables name of X, Y, Z etc. These are 8 unique characters ID. Is there a way to pair them up according to their ID and do the multiplication? Else I have to explicit write out the mulitiplication as you did for 1000 IDs.

Thank you

Leo

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to CheerfulChu

03-07-2016 01:27 PM

Could you please tell us realistinc names of the variables for 5 pairs? If you have 1000 pairs of variables, then obviously the prefixes are more complicated than "A_" through "Z_".

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Rick_SAS

03-07-2016 01:34 PM

Hi Rick,

Sure.

ie ASU0T12 ASU11GT BSU891G. 7 characters using A-Z and 0-9

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to CheerfulChu

03-07-2016 09:07 PM

I think declaring three arrays - one to hold x, one to hold y and one to hold results. You need to ensure the variables are listed in the same order in the array declaration. If you have some rules you can use sashelp.vcolumn to develop macro variables that hold the value.

Data want;

Set have;

Array x(1000) x_1-x_1000;

Array y(1000) y_1-y_1000;

Array r(1000) r_1-r_1000;

Do i=1 to dim(x);

R(i)=x(i)*y(i);

End;

Run;

But having a data set that is 3000 columns sounda horrid. I would go with the transpose solution. You can look up a paper called a better way to flip that has a macro to easily transpose data sets.

Data want;

Set have;

Array x(1000) x_1-x_1000;

Array y(1000) y_1-y_1000;

Array r(1000) r_1-r_1000;

Do i=1 to dim(x);

R(i)=x(i)*y(i);

End;

Run;

But having a data set that is 3000 columns sounda horrid. I would go with the transpose solution. You can look up a paper called a better way to flip that has a macro to easily transpose data sets.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to CheerfulChu

03-08-2016 12:22 AM

Assuming the order as what you showed. you can use the following code to name variables.

```
data have;
input Date X_height X_weight Y_height Y_weight;
cards;
1990 100 50 200 40
1991 80 40 300 30
;
run;
data temp;
set sashelp.vcolumn(keep=libname memname name
where=(libname='WORK' and memname='HAVE' and upcase(name) ne 'DATE'));
flag=mod(_n_,2);
run;
data _null_;
merge temp(keep=name flag where=(flag=1))
temp(keep=name flag where=(flag=0) rename=(name=_name)) end=last;
if _n_ eq 1 then call execute('proc sql;create table want as select date');
call execute(',');
call execute(cat(strip(name),'*',strip(_name),' as ',strip(scan(name,1,'_')),'_',strip(scan(name,2,'_')),'_',strip(scan(_name,2,'_')))) ;
if last then call execute('from have;quit;');
run;
```