Hello,
I have a file that looks like the attached file.
I need to transform it to something like the sample table below. I'm assuming I need to do some kind of looping, but don't know how to do this. Please advise.
Please provide data as text and the rules relating input and desired output. I can't guess from what you posted.
I solved my own problem just now. It's probably not the most efficient but it works.
Sample data attached.
Sample code below gives the desired result.
/*Pull ID and create UserID and UserName from source data*/
proc sql;
create table TestIDs as
select ID as UserID, catx('', 'Test', ID) as UserName
from DashboardVars;
quit;
/*appends the file by the number of IDs in the file; also create new ID variable*/
data TestIDs2(drop=i);
set TestIDs;
do i = 1 to 28;
ID = i;
output;
end;
run;
/*if UserID and ID match, then it is assigned MyGroup*/
data TestIDs3;
set TestIDs2;
if UserID = ID then Group = "MyGroup";
else Group = "Others";
run;
/*left join original source file by ID*/
proc sql;
create table DashboardVarsNew as
select t1.*, t2.*
from TestIDs3 t1
left join DashboardVars t2 on t1.ID = t2.ID
order by UserID, ID;
quit;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.