topic Re: Long to wide Ronein in SAS Programming

Long to wide Ronein

Ronein — Mon, 01 Dec 2025 11:48:50 GMT

Hello

In real life I have a big data set 17 million rows and many offer_ID's.

Is there a better way to perform convertion from long to wide .

Please see the current code


data have;
input offerID degem amnt;
cards;
111123 200 1000 
111123 300 2000
111123 400 3000
;
run;
PROC TRANSPOSE DATA=have OUT=wide1(DROP=_name_) prefix=degem_;
BY offerID; 
VAR degem;
RUN;
PROC TRANSPOSE DATA=have OUT=wide2(DROP=_name_) prefix=Amnt_;
BY offerID; 
VAR degem;
RUN;
Data want_Wide;
merge wide1 wide2;
BY offerID; 
Run;

Re: Long to wide Ronein

PaigeMiller — Mon, 01 Dec 2025 14:50:33 GMT

IMHO

The best thing to do with this data set is to leave it as a long data set, and perform any analyses on the long data set. But you probably knew that already. The less processing you have to do on 17 million records, the better off you will be.

Please explain why you want it to be a wide data set.

Re: Long to wide Ronein

Kathryn_SAS — Mon, 01 Dec 2025 12:48:18 GMT

This still requires multiple steps, but here is another idea.

data have;
input offerID degem amnt;
cards;
111123 200 1000 
111123 300 2000
111123 400 3000
;
run;

data have;
set have ;
by offerid;
if first.offerid then count=0;
count+1;
run;

proc transpose data=have out=wide;
by count offerid;
var degem amnt;
run;

data wide;
set wide;
varname=catx('_',_name_,count);
run;

proc transpose data=wide out=wide1(drop=_name_);
by offerid;
id varname;
var col1;
run;

proc print;
run;

Re: Long to wide Ronein

data_null__ — Mon, 01 Dec 2025 14:37:06 GMT

Proc transpose has features that allow you to skip the step where you create VARNAME.

proc transpose data=wide out=wide1(drop=_name_) delim=_;
   by offerid;
   *id varname;
   id _name_ count;
   var col1;
   run;

Re: Long to wide Ronein

Ronein — Mon, 01 Dec 2025 14:59:36 GMT

At my work have task to create a panel data which has one row ( wide structure) instead of long structure ( multiple rows per offer id). I know it is strange but for most of people it is easier to look at wide structure data where each offer id has one row

Re: Long to wide Ronein

PaigeMiller — Mon, 01 Dec 2025 17:28:24 GMT

And what analysis will you be doing?

When I look at the examples in PROC PANEL they are all long data sets, not wide data sets. Most SAS data analysis PROCs are designed to use long data sets.

Re: Long to wide Ronein

Tom — Mon, 01 Dec 2025 17:49:20 GMT

If you know the maximum number of DEGEMs or AMTs per OFFERID then it is simple using ARRAYs.

data have;
  input offerID degem amnt;
cards;
111123 200 1000 
111123 300 2000
111123 400 3000
;

data want;
  set have;
  array _degem degem1-degem3 ;
  array _amnt amnt1-amnt3;
  do _n_=1 by 1 until(last.offerid);
    set have;
    by offerid;
    _degem[_n_]=degem;
    _amnt[_n_]=amnt;
  end;
  drop degem amnt;
run;

If you don't know the number you have two choices. Just pick something that you know is too large.

Or calculate it. For example you could use PROC SQL to create a macro variable.

proc sql noprint;
select max(count) into :max_count trimmed
from (select offerid,count(*) as count 
       from have group by offerid)
;
quit;

Then use that macro variable to generate the arrays' upper bounds.

  array _degem degem1-degem&max_count ;
  array _amnt amnt1-amnt&max_count ;

Re: Long to wide Ronein

Kurt_Bremser — Mon, 01 Dec 2025 20:20:26 GMT

If people need to look at it, it's a report, not a dataset. Hand them the PROC REPORT code so they can run it on the intended subset (nobody will manually inspect 17 million observations in a single lifetime).

Re: Long to wide Ronein

Kurt_Bremser — Mon, 01 Dec 2025 20:23:23 GMT

And another thing: if there's just one ID with lots of observations, the resulting dataset will be enormous and consist mainly of missing values.

Re: Long to wide Ronein

Ksharp — Tue, 02 Dec 2025 07:27:04 GMT

1)The most simple way is using PROC SUMMARY, but the limited thing is the max number of row in each 'offerID' group is 100 .


data have;
input offerID degem amnt;
cards;
111123 200 1000 
111123 300 2000
111123 400 3000
;
run;

proc sql noprint;
select max(n) into :n
 from (select count(*) as n from have group by offerID);
quit;

proc summary data=have;
by offerID;
output out=want(drop=_freq_ _type_) idgroup(out[&n.]  (degem amnt)=);
run;

2)My favorite way is using MERGE skill , but that need you to familiar with sas programming.

data have;
input offerID degem amnt;
cards;
111123 200 1000 
111123 300 2000
111123 400 3000
;
run;

data temp;
 set have;
 by offerID;
 if first.offerID then n=0;
 n+1;
run;

proc sql;
create table level as
select distinct n from temp;
quit;

data _null_;
 set level end=last;
 if _n_=1 then call execute('data want;merge ');
 call execute(catt('temp(where=(n=',n,') rename=(degem=degem_',n,' amnt=amnt_',n,'))'));
 if last then call execute(';by offerID; drop n; run;');
run;

Re: Long to wide Ronein

ballardw — Wed, 03 Dec 2025 22:44:01 GMT

If the purpose is to look for commonality between the variables you might try a report such as this:

Proc tabulate data=have;
   class offerid degem amnt;
   table offerid,
           degem amnt
          /misstxt=' '
   ;
   keylabel n=' ';
run;

This will have one row for each offerid. The columns for degem and amnt will have the count of the number of times each value appears for that offerid and a blank space where there are none (the option MISSTEXT does the blank). The keylabel supresses the N that would otherwise appear in each column for the statistic.

If you don't want a count but just an indicator then a custom format to display all the values 1 or greater as a single character could be created and used.

Note: this doesn't care what variable type any of the three variables are.

Re: Long to wide Ronein

qingy — Tue, 09 Dec 2025 03:23:45 GMT

I took a different approach, using the Lua procedure.

The initial step using SQL tells us how wide we need the table to be. We then use that information to generate the blank Got table with the required columns. Finally we parse the Have table into the Got table. In this example, we excluded blank records as a demonstration. I find this type of control structure to be much simpler in Lua than in base SAS.

data have;
input offerID degem amnt;
cards;
111123 200 1000 
111123 300 2000
111123 400 3000
111124 500 4000
111124 . .
111124 550 4500
11125 600 5000
;
run;

proc sql;
	select max(numRecs) into :width
	from
	(select offerID, count(*) as numRecs
		from
		have
		group by offerID
	)
	;
quit;

proc lua;
	submit;
	local have = sas.open("have")
	local width = tonumber(sas.symget("width"))
	
	local want_vars = { {name = "offerID", type="N"} }
	
	for idx=1,width do
		table.insert(want_vars, {name = string.format("degem_%i",idx), type = "N"})
	end
	
	for idx=1,width do
		table.insert(want_vars, {name = string.format("Amnt_%i",idx), type = "N"})
	end
	
	sas.new_table("got", want_vars)
	local got = sas.open("got", "u")
	
	local curr_id
	local idx = 0
	for row in have:rows() do
		if row.offerid ~= curr_id then
			if curr_id then
				got:update()
			end
			got:append()
			curr_id = row.offerid
			got:put_value("offerID", curr_id)
			idx = 0		
		end
		if sas.missing(row.degem) == 0 or sas.missing(row.amnt) == 0 then
			idx = idx + 1
			got:put_value(string.format("degem_%i",idx), row.degem)
			got:put_value(string.format("Amnt_%i",idx), row.amnt)
		end
	end
	got:update()
	have:close()
	got:close()
	endsubmit;
run;