Equivalent to rbind under for loop in sas

[Souradip]

Hi guys,
Looking for solution. I want to replicate a code from R to SAS.

Data:
C1 C2
A. 10
B. 20
A. 40
C. 5
B. 55

R code:

Result= data.frame()
Unq= unique (data$C1)

for( i in Unq)
{
i_df=subset ( data, C1==i)

(Some operation)
Result=rbind (Result, i_df)
}

How to do this by SAS. By using loop, not SQL .

Thanks.

[PeterClemmensen]

Hi and welcome to the SAS communities 🙂

Since this is a SAS community, not all users are familiar with R syntax. Therefore it is a good idea to show us the desired result from your posted example data.

[Ksharp]

If I understood this.

proc sort data=have;
by c1;
run;

..(Some operation).....

[Reeza]

You would use BY group processing in SAS not split the data set and combine. Somewhat similar to GROUP_BY in tidyverse but not quite the same. The other approach would be a macro, but I suspect a macro is overkill.

You can probably get it working if you try and do line by line conversion but you’re better off using a SAS optimized approach.

If you explain in more detail perhaps we can help more.

[Tom]

Since you didn't explain what rbind() does I turned to the trusty Google machine.

If you want to APPEND a dataset to the end of another dataset then use PROC APPEND.

proc append base=result data=i_df ;
run;

[Reeza]

This paper goes over why this type of approach is not recommended in SAS. As part of that, it starts out with the looping method, which is inefficient in SAS compared to BY groups.  

https://support.sas.com/resources/papers/proceedings/proceedings/forum2007/183-2007.pdf

---

@Souradip wrote:
Hi guys,
Looking for solution. I want to replicate a code from R to SAS.

Data:
C1 C2
A. 10
B. 20
A. 40
C. 5
B. 55

R code:

Result= data.frame()
Unq= unique (data$C1)

for( i in Unq)
{
i_df=subset ( data, C1==i)

(Some operation)
Result=rbind (Result, i_df)
}

How to do this by SAS. By using loop, not SQL .

Thanks.

---

[Souradip]

Thanks for your comments. Here I create a very simple example for the above. Inputs are

Data 1:
Year
2012
2013
2014

Data2:
State year value
A. 2012. 4
A. 2013. 6
B. 2013. 10

Output:
State. Year. Value
A. 2012. 4
A. 2013. 6
A. 2014. 0
B. 2012. 0
B. 2013. 10
B. 2014. 0

We might have a number of states which might not be possible to execute one by one manually.

[Reeza]

Is your ultimate goal to create a table with all possible values?

If your table 2 will have at least one value in each group there's an easy fix. If not, the fix is slightly more complex but still relatively easy. 

For something like this, a SQL query is pretty robust to work with. 

data data1;
do year=2012 to 2014;
output;
end;
run;

data data2;
input state $ year value;
cards;
A 2012 4
A 2013 6
B 2013 10
;;;;
run;

proc sql;
create table want as
select t1.state, t1.year, case when not missing(t2.value) then t2.value else 0 end as newValue
from (select state, year from (select distinct state from data2), (select distinct year from data1)) as t1
left join data2 as t2
on t1.state=t2.state and t1.year=t2.year;
quit;

Other options:

1. SPARSE with PROC FREQ if every value is present

2. CLASSDATA option with PROC MEANS or summary proc

3. PRELOADFMT with a summary proc such as PROC MEANS. That would work here as well. 

Here's a short video on some of those options, I usually like Option 1, but its not the most robust. 

http://sasgf16.v.sas.com/detail/videos/breakout-sessions/video/4854738295001/10600---you-can-bet-on-...

[Souradip]

Thanks. 🙂

[KrunalLathiya]

To merge two data frames (datasets) horizontally, use the merge() function in the R language. The cbind() function combines R Objects By Rows Or Columns.

The cbind() and rbind() functions are generic methods for data frames. These data frame functions will be used if at least one argument is a data frame and the other arguments are vectors or matrices. To merge two data frames (datasets) horizontally, use the merge() function in the R language.

Loops are the most frequently used snippet in any programming language, and R is no different. All modern programming languages implement special structures that allow the repetition of code instructions.