Have
year name sales
2001 john 1000
jack 2000
2002 john 3000
jack 4000
Want
year name sales year name sales
2001 john 1000 2002 john 3000
2001 jack 2000 2002 jack 4000
How can I get the above output in excel ? please check have and want tables.
data have;
input year name $ sales;
id=name;
cards;
2001 john 1000
2001 jack 2000
2002 john 3000
2002 jack 4000
;
proc sort data=have;by name;run;
proc sql noprint;
select distinct catt('have(rename=(year=year',year,' name=name',year,' sales=sales',year,')
where=(year',year,'=',year,'))') into : merge separated by ' '
from have ;
quit;
data want;
merge &merge. ;
by id;
drop id;
run;
Looks like proc transpose could be used, but only if there are no missing values in "year".
To get further help, you may want to post your data in usable form. The data-layout you want is in general not recommended, except for reports.
The below is fine too
Have
year name sales
2001 john 1000
2001 jack 2000
2002 john 3000
2002 jack 4000
Want
year name sales year name sales
2001 john 1000 2002 john 1000
2001 jack 2000 2002 jack 2000
@vijay1 wrote:
The below is fine too
Have
year name sales
2001 john 1000
2001 jack 2000
2002 john 3000
2002 jack 4000
Want
year name sales year name sales
2001 john 1000 2002 john 1000
2001 jack 2000 2002 jack 2000
I hope you realize that you can't have data sets with multiple columns each with identical variable names. You can have SALES as the variable name only once. You can have YEAR as the variable name only once.
Also, why do you want this? What is the next step? Are you going to produce a report, or are you going to do some data analysis? What? Normally this type of re-arranging of data is not going to be useful (although there are exceptions) and makes subsequent programming more difficult.
I made the correction:
Have
year name sales
2001 john 1000
2001 jack 2000
2002 john 3000
2002 jack 4000
Want
year name sales year name sales
2001 john 1000 2002 john 3000
2001 jack 2000 2002 jack 4000
Are you sure that's what you want?
Wouldn't something like this make more sense:
Name Sales2001 Sales2002
John 1000 3000
Jack 2000 4000
And most importantly, HOW are the numbers changing? How do you go from 3000 to 1000?
@vijay1 wrote:
The below is fine too
Have
year name sales
2001 john 1000
2001 jack 2000
2002 john 3000
2002 jack 4000
Want
year name sales year name sales
2001 john 1000 2002 john 1000 <- What is the rule to change the values?
2001 jack 2000 2002 jack 2000
@vijay1 wrote:
The below is fine too
No, it is not. As others already said: you can't have multiple variables with the same name in a dataset. So until you have fixed the obvious issue in your wanted result, it is hardly possible to help you. It is also still not clear whether you want the result as report or dataset, the later would raise the question what you plan to do with such a dataset.
If you want a report, check the following code, maybe this layout is acceptable, too.
proc sort data=have out=sorted;
by name year;
run;
proc report data=sorted;
columns name year,sales;
define name / group;
define year / across;
define sales / sum; /* only one value per name/year so no effect */
run;
Do you want a report, people read these, or a data set for further manipulation? If you want a data set SAS will not allow you to have multiple variables with the same name. You would have to have something in the output like Name2001 and Name2002
Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values as your SAS data set.
@vijay1 wrote:
Have
year name sales
2001 john 1000
jack 2000
2002 john 3000
jack 4000
Want
year name sales year name sales
2001 john 1000 2002 john 1000
jack 2000 jack 2000
How can I get the above format? please check have and want tables. attached the same excel file too
data have;
input year name $ sales;
id=name;
cards;
2001 john 1000
2001 jack 2000
2002 john 3000
2002 jack 4000
;
proc sort data=have;by name;run;
proc sql noprint;
select distinct catt('have(rename=(year=year',year,' name=name',year,' sales=sales',year,')
where=(year',year,'=',year,'))') into : merge separated by ' '
from have ;
quit;
data want;
merge &merge. ;
by id;
drop id;
run;
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.