topic Re: manipulate data query - transpose in SAS Programming

manipulate data query - transpose

dennis_oz — Tue, 12 Jan 2021 05:14:59 GMT

Hi All,
I have a query , which is the best way to transpose date .  
i would like get a single customer view . Can you advice .. 



data a;
input visit_count cust_no year_contact branch_no;
datalines;
8 165670  2017 5308 
4 165670  2018 5308 
3 165670  2019 5308
2 165670  2020 5308 
;
run;

Re: manipulate data query - transpose

Aku — Tue, 12 Jan 2021 07:00:26 GMT

I'm not sure if this is what you want:

data a;
input visit_count cust_no year_contact branch_no;
datalines;
8 165670 2017 5308
4 165670 2018 5308
3 165670 2019 5308
2 165670 2020 5308
;
run;
proc sort data=a;
by cust_no branch_no ;
run;
proc transpose data=a out=a_TR prefix=Year;
by cust_no branch_no ;
var visit_count;
id year_contact;
run;

Re: manipulate data query - transpose

ballardw — Tue, 12 Jan 2021 07:01:28 GMT

I assume by "single customer view" that you mean you want all the data associated with a customer on a single row in a data set. Is that correct?

If so, what are you going to do with data in that form that you cannot do with it in the current form?

If you want a report that reads as such that is one thing, maybe something like:

proc report data=a;
  columns cust_no year_contact,( branch_no visit_count);
  define cust_no / group;
  define year_contact /across "";
run;

When you make the data set "wide", with multiple similar values then you have to create multiple variables.

Such as 4 each year, branch and count variables for your given example. But unless every one of your customers have the exact same number of records you will have some customers with one of the variables populated and for longer term customers you could have 20 or more year variables and associated others. Which can make it hard to some things because you spend a lot of time parsing values out to find things like matching years across customers to get the same periods.

So, please provide examples of the tasks you need to perform with the data that require a wide format.

Quite often we see this question because the only training or experience new SAS users have comes from working with spreadsheets. SAS is not a spreadsheet. It has different abilities and for a vast majority of tasks the form of data that you have currently is more flexible.

Re: manipulate data query - transpose

Ksharp — Tue, 12 Jan 2021 11:27:57 GMT

what output do you like to see ?


data a;
input visit_count cust_no year_contact branch_no;
datalines;
8 165670  2017 5308 
4 165670  2018 5308 
3 165670  2019 5308
2 165670  2020 5308 
;
run;

%let dsid=%sysfunc(open(a));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));

proc summary data=a ;
by cust_no;
output out=want idgroup(out[&nobs] (visit_count year_contact branch_no)=);
run;

Re: manipulate data query - transpose

PhilC — Thu, 14 Jan 2021 19:57:44 GMT

This is an example of PROC TABULATE which can summarize this data creating one "view" per customer.

PROC TABULATE DATA=WORK.A	FORMAT=4.	;
	
	VAR visit_count;
	CLASS year_contact 
	      branch_no    
	      cust_no      /	ORDER=UNFORMATTED MISSING;

	TABLE cust_no*visit_count*Sum=" ",
        branch_no,
        year_contact
	;

RUN;