topic Re: concatenate observation using proc sql in SAS Programming

concatenate observation using proc sql

Sir_Highbury — Thu, 30 Jun 2016 13:35:59 GMT

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

Re: concatenate observation using proc sql

Reeza — Thu, 30 Jun 2016 13:46:23 GMT

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

Re: concatenate observation using proc sql

Kurt_Bremser — Thu, 30 Jun 2016 13:56:49 GMT

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;

Re: concatenate observation using proc sql

Reeza — Thu, 30 Jun 2016 14:04:53 GMT

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.

Re: concatenate observation using proc sql

Sir_Highbury — Thu, 30 Jun 2016 14:57:14 GMT

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.

Re: concatenate observation using proc sql

Sir_Highbury — Thu, 30 Jun 2016 15:06:45 GMT

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.

Re: concatenate observation using proc sql

Reeza — Thu, 30 Jun 2016 15:14:54 GMT

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.

Re: concatenate observation using proc sql

Sir_Highbury — Thu, 30 Jun 2016 15:20:28 GMT

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.

Re: concatenate observation using proc sql

Reeza — Thu, 30 Jun 2016 15:24:09 GMT

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.

Re: concatenate observation using proc sql

Sir_Highbury — Thu, 30 Jun 2016 15:33:36 GMT

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 🙂

Re: concatenate observation using proc sql

Reeza — Thu, 30 Jun 2016 15:43:46 GMT

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.

Re: concatenate observation using proc sql

Sir_Highbury — Mon, 04 Jul 2016 12:50:05 GMT

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.

Re: concatenate observation using proc sql

Reeza — Mon, 04 Jul 2016 12:56:33 GMT

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.

Re: concatenate observation using proc sql

Sir_Highbury — Mon, 04 Jul 2016 13:35:02 GMT

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. -##

Re: concatenate observation using proc sql

Sir_Highbury — Mon, 04 Jul 2016 14:19:29 GMT

assuming you would do the same within a macro:

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;

I changed the code as it follows:

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

do you already notice a mistake? Instead of getting the value list I get the variable name....

Re: concatenate observation using proc sql

Reeza — Mon, 04 Jul 2016 14:29:56 GMT

What does &avatar resolve to? And what are you getting?

I don't see any issues with the code.

PS. I didn't mean any offence with previous response 🙂

Re: concatenate observation using proc sql

Sir_Highbury — Mon, 04 Jul 2016 14:47:44 GMT

1. I have a macro which is based on the variable &var;

2. A call execute is used;

If for istance the &var assume the variable "segment", I did not get as output the values assumed by the variable "segment" but only the name of the variable "segment" itself.

The example works perfectly, but when I switched to the macro I did something wrong.

Re: concatenate observation using proc sql

Reeza — Mon, 04 Jul 2016 14:54:04 GMT

I don't have enough info to help.

I think ink this may warrant a new question now, with more details. Usually with call execute it becomes an issue of timing - when it executes versus values that are passed.

Re: concatenate observation using proc sql

Sir_Highbury — Mon, 04 Jul 2016 15:00:10 GMT

ok, fair enough. I will keep trying and in case I will post something new.

Re: concatenate observation using proc sql

Tom — Mon, 04 Jul 2016 15:02:14 GMT

If you want to use the macro variable to refer to a variable then it needs to contain the NAME of the variable.

%let var=segment;

From the symptom it looks like you have set the macro variable to a value that includes quotes.

%let var="segment";

This will be interpreted by regular SAS as a string literal and not the name of a variable.