Sql

[Emma2021]

Can I change those below sas codes into SQL?

Proc sort data=dsn; by id;

Proc transpose data=dsn; out=dsn_1;
By id;
Id name;
Var var1;
Run;

Thank you.

[Reeza · Accepted solution]

Transposes are hard to do in SQL. In SAS SQL you would have to hard code the new variable names for example. Some SQL DB do support transpose using PIVOT or other similar functionality but none as nicely as PROC TRANSPOSE.

So technically you can, but the exact code depends on the data and if the data changes the code has to change.

---

@Emma2021 wrote:
Can I change those below sas codes into SQL?

Proc sort data=dsn; by id;

Proc transpose data=dsn; out=dsn_1;
By id;
Id name;
Var var1;
Run;

Thank you.

---

[Daniel_Enache]

Hello,

as in the previous answer, I think that there is no general solution.

However, there is a solution, if you know the categories of your transpose-id-variable (in you case "Name"). Since I don't know your data set, I will use the class dat set shipped with SAS:

data Test;
	set sashelp.class;
run;
proc sort data=Test; by Name Sex; run;
proc transpose data=Test out=Test_1;
	id Sex;
	by Name;
	var Height;
run;

The Variable Sex has 2 categories 'F' and 'M'. If those are not changing, you can write:

proc sql;
	create table Test_2 as
		select Name,
			max( case when Sex = 'M' then Height else . end ) as M,
			max( case when Sex = 'F' then Height else . end ) as F
		from Test
		group by Name
		order by Name;
quit;

However, if those categories are changing, you will need to program a SAS-Makro to retrieve the categories into a list and do a loop:

%macro transCustom(intab, outtab, idvar, byvar, varvar);
	%local i numid listid;

	/* Get categories and store in a list. */
	proc sql noprint;
		select distinct &idvar.
		into :listid separated by ' '
		from &intab.
		order by &idvar.;
	quit;

	/* Number of elements in list. */
	%let numid=%sysfunc(countw(&listid.));

	proc sql;
		create table &outtab. as
			select &byvar.
				%do i=1 %to &numid.;
					%let curcat=%scan(&listid., &i.); /* Get i-th element from list. */
					, max( case when &idvar. = "&curcat." then &varvar. else . end ) as &curcat.
				%end;
			from &intab.
			group by &byvar.
			order by &byvar.;
	quit;
%mend;
%transCustom(Test, Test_3, Sex, Name, Height);

I hope this helps,

Daniel.