concatenate observation using proc sql

[Sir_Highbury]

Dear expert,

considering the initial data set:

data have;
  country="BU"; output;
  country="JA"; output;
  country="US"; output;
run;

Considering the following code that already works:

data want (drop=country rename=(tmp=country));
  length tmp $2000;
  set have end=last;
  retain tmp;
  tmp=catx(",",tmp,country);
  if last then output;
run;

is there another short way of doing it, maybe using the proc sql? Thanks, SH

[Reeza]

Not via SQL unless your trying to create a macro variable?

[Kurt_Bremser · Accepted solution]

Using a macro variable as intermediate storage:

proc sql noprint;
select country into :country separated by ','
from have;
create table want (country char(2000));
insert into want values("&country");
quit;

[Reeza]

I'm going to comment on something, feel free to ignore this. 

From your previous questions it sounds like your moving to SAS from a different language and trying to replicate some sort of process or at least that method of working. It also sounds like your either frustrated or angry at this. SAS works differently, but has its own efficiencies, with the things like _numeric_, variable lists, supporting prefix shortcuts and BY group processing. If you try to use SAS, the SAS way, versus trying to cram it into the R/Python way it will be easier in the long run. If you do want to use R/Python methods, IML may be worth investigating. 

My nickel of thought. Good Luck. 

[Sir_Highbury]

Dear Reza,

thanks for your moral support but I am not frustrated, I happy to work with SAS. The scope of my job is not playing/learning SAS for fun, but to do what I need helped by SAS. Therefore I wanna do it in a for me understandable and fast manner, this is way I am using the forum. With the time I will be a bit more SAS friendly.

Unfortunately I cannot use r and Python or Apache Spark (where you have all together Python, r, sql, django....) and IML due to legal constrains. Based on my understanding you do not offer r or Python inside SAS (like for sql) but I would need to install separately r and Python.

[Sir_Highbury]

Another comment: to learn python/r there are online thousend of courses offered for free online by top universities (e.g. MIT, Berkley).

Despite of paying the SAS licence, you even ask more money (SAS courses are really expensive) to the people that already pay for the licence and are using SAS. This is your business model, I am not willing to pay also for expensive courses. I am one of the thousends of students in Germany that used Python and r for free and fully satisfied.

[Reeza]

Tutorials and 2 e-courses for free

http://support.sas.com/training/tutorial/index.html

Search any topic here - if you can't find a paper that would be surprising

http://www.lexjansen.com

Commercial software does have different training models, this is more in line with Tableau or SPSS. 

SAS UE provides SAS software for free to anyone interested in learning. It's a step forward for a company as old as SAS. 

[Sir_Highbury]

do you mean that I can install SAS at home on my PC? Thanks for the links, I will have a closer look. I never used Tableu or SPSS. 

[Reeza]

http://www.sas.com/en_us/learn/analytics-u.html

Yes, You can install on SAS on your home PC, run off AWS, or use a install that runs of NC via Academics On Demand. Limitations are for non commercial usage. Research is allowed. 

[Sir_Highbury]

sounds good... I will surely try to benefit from these resources.

When I asked your company for learning possibilities, I got ONLY a list of expensive courses. Nobody mentioned to me it. The question is why? I can imagine an answer 🙂

[Reeza]

It's not my company, I'm a SAS user, like yourself. I also program in several other languages, but SAS is my primary language. 

[Sir_Highbury]

if SAS is not your company but you are working for a different one, just for the sake of curiositiy, why do you participate to this forum answering? I would expect it only on "free projects" like linux forum or stack overflow.

[Reeza]

Lol. 

There are many community forums that exist for proprietary softwares that predate SO. Remember SO was only invented in 2008, SAS has been around since 1970's. I've been using a SAS forum of some sort since 2005. 

This is is a free forum for users and contributors. The fact that SAS maintains it is irrelevant except to show their dedication to their user base. 

Other examples of proprietary software user groups are Tableau and MS SQL. 

In my experience these user groups flourish when they have dedicated staff attached to them. The R user group in the cities I lived in by comparison didn't meet regularly or do much.

Why would volunteering on SO or here be any different? The SAS user base here is much larger and if a question isn't answered SAS will try to answer it. If no one on SO answers your question your pretty much out of luck. 

[Sir_Highbury]

it was just a question, I did not have any particular expectations. You
could be a philanthropy or a sas consultant looking for clients or
whatever. Nothing against any motivation,

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Kurt_Bremser]

---

@Sir_Highbury wrote:

if SAS is not your company but you are working for a different one, just for the sake of curiositiy, why do you participate to this forum answering? I would expect it only on "free projects" like linux forum or stack overflow.

---

There is a certain type of satisfaction coming out of a problem solved.

It is this satisfaction that is also part of the forces that drive open source programmers.

Getting an "Attaboy" in the form of a like or an answer marked as solution is another thing.

And every couple of days of participating here, I learn something new for myself that may help me later here in my paid-for work.

(local data processing division of a large international insurance company)