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

Hello

 I have two datasets, with 4 rows and 3 columns. and I need to divide the values of one by the other.

dataset 1

                  Col1           Col2         Col3

Type1       T1C1          T1C2         T1C3

Type2       T2C1          T2C2         T2C3

Type3       T3C1          T3C2         T3C3

Type4       T4C1          T4C2         T4C3

 

dataset 2

                  Col1           Col2         Col3

Samp1       S1C1          S1C2        S1C3

Samp2       S2C1          S2C2        S2C3

Samp3       S3C1          S3C2        S3C3

Samp4       S4C1          S4C2        S4C3

 

 

How can I divide the values in dataset 1 by the values in dataset2:

desired result:

dataset 3:

                  Col1                        Col2                    Col3

Resl1       T1C1/S1C1          T1C2/S1C2        T1C3/S1C3

Resl2       T2C1/S2C1          T2C2/S2C2        T2C3/S2C3

Resl3       T3C1/S3C1          T2C2/S3C2        T2C3/S3C3

Resl4       T4C1/S4C1          T4C2/S4C2        T4C3/S4C3

 

I ws thinking of using a loop:

data result;
        set dataset1 dataset2;

array type{4,3} t1c1-t1c3 t2c1-t2c3 t3c1-t3c3 t4c1-t4c3;

array sample{4,3} s1c1-s1c3 s2c1-s2c3 s3c1-s3c3 s4c1-s4c3;

        %do i = 1 %to dim1(type);
            %do j = 1 %to dim2(type);
             result{i,j}=type{i,j}/sample{i,j};
            %end;
        %end;

 

thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
Are you using IML? If not, then you don't need to a multidimensional array or a macro.

MERGE the do data sets (side by side) declare two arrays and loop through.


Data want;
merge data1 data2;
array d1(3) d1_col1-d1_col3; *column names from first data set;
array d2(3) d2_col1-d2_col3;*column names from second data set;

array d3(3) d3_col1-d3_col3;

do i=1 to 3;
d3(i)=d1(i)/d2(i);
end;

run;

View solution in original post

5 REPLIES 5
Reeza
Super User
Are you using IML? If not, then you don't need to a multidimensional array or a macro.

MERGE the do data sets (side by side) declare two arrays and loop through.


Data want;
merge data1 data2;
array d1(3) d1_col1-d1_col3; *column names from first data set;
array d2(3) d2_col1-d2_col3;*column names from second data set;

array d3(3) d3_col1-d3_col3;

do i=1 to 3;
d3(i)=d1(i)/d2(i);
end;

run;
Kc2
Quartz | Level 8 Kc2
Quartz | Level 8

No I am not using IML

Reeza
Super User
Then you should review how SAS processes data.
For starters, SAS automatically loops through each row. An array is used as a shortcut reference to variable names. So you don't need to set up a matrix and treat the data as multidimensional array because SAS will process it the way you want automatically. You have some code suggestions, but some other key concepts are that you can't have columns with the same name, so you need to rename one set of columns from a data set.
Kc2
Quartz | Level 8 Kc2
Quartz | Level 8

Yes I did rename the colums in one of the dataset. Thank you for your help. It worked.

slchen
Lapis Lazuli | Level 10


data want;
merge dateset1 dataset2(rename=(col1=_col1 col2=_col2 col3=_col3));
array var1 col:;
array var2 _col:;
do over var1;
var1=catx('/',var1,var2);
end;
drop _:;
run;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1551 views
  • 0 likes
  • 3 in conversation