topic Re: Creating variables from existing variables in SAS Programming

Creating variables from existing variables

blowsnow__ — Sat, 03 Nov 2018 14:18:48 GMT

Greetings all,

I have the following data set stored as a .txt file. How can I use only one DATA step to produce an output that only contains one observation for each variable? My ultimate goal is to have the output as the attached picture. I know I am currently missing a few other variables, but how can I create those variables from the existing ones? This is what I have so far.

data testing;
infile datalines firstobs=2;
input name $ 1-15 latest_sales_date:date11. person_total_sales 28-32;
format date date11.;
datalines;
1234567890123456789012345678901234567890
David Wong 1/Aug/2018 13200
Brian Leung 15/Sep/2018 23450
10/Sep/2018 33000
Mary Chan 3/Jul/2018 45600
20/Aug/2018 37800
1/Sep/2018 21500
30/Aug/2018 42000
John Tam 12/Sep/2018 35000

run;

Re: Creating variables from existing variables

VDD — Sat, 03 Nov 2018 14:56:37 GMT

given your sample data @blowsnow__ this should work.

data testing;
infile datalines firstobs=2;
input name $ 1-15 latest_sales_date date11. person_total_sales 28-32;
format latest_sales_date date11.;
/*1234567890123456789012345678901234567890*/
datalines;

David Wong     1/Aug/2018  13200 
Brian Leung    15/Sep/2018 23450
               10/Sep/2018 33000
Mary Chan      3/Jul/2018  45600
               20/Aug/2018 37800
               1/Sep/2018  21500
               30/Aug/2018 42000
John Tam       12/Sep/2018 35000
run;

data temp;
	 set testing;
	 retain holder;
	 if not missing(name) then holder=name;
run;
proc sort data=temp out=temp;
	by holder latest_sales_date;
run;


data want(drop=holder hdate person_sales_total);
	 retain days person_sales_total 0 hdate ;
	 set temp;
	 by holder latest_sales_date;
	 running_total + person_total_sales;
	 Person_sales_total + person_total_sales;
	 if first.holder then hdate = latest_sales_date;
	 if last.holder then days = (latest_sales_date - hdate);
	 name=holder;
	 person_total_sales = person_sales_total;
	 if last.holder then 
		do;
			output;
			person_total_sales = 0;
		end;
run;

Re: Creating variables from existing variables

novinosrin — Sat, 03 Nov 2018 14:50:30 GMT

Hi @blowsnow__ is your expectation one datastep or one pass of the dataset?

Plz clarify as within a datastep , we can have multiple passes

Re: Creating variables from existing variables

blowsnow__ — Sat, 03 Nov 2018 14:58:19 GMT

The condition is that I must use only one datastep

Re: Creating variables from existing variables

VDD — Sat, 03 Nov 2018 15:04:29 GMT

that changes what I have already posted. It doesn't meet the requirements since the requirements were not fully stated.

Re: Creating variables from existing variables

Tom — Sat, 03 Nov 2018 15:06:19 GMT

You just need to read the name value into a dummy column and then save it into the real Name variable when it is not empty. Use RETAIN the carry the old values of NAME forward.

Here is sketch of what you need to do.

input dummy $ 1-15 ...... ;
name=coalescec(dummy,name);
retain name;

Single observation for each variable

blowsnow__ — Sat, 03 Nov 2018 17:08:31 GMT

Greetings all,

I am trying to read a data set into the following table.

When I try to run my program, however, the Name column contains more than one observation for each person. What can I do to make the output contains only one name for each person? And the same goes for the date column, how can I only select the most recent date to show in my output table? Here is what I have so far.

data Sales_Total ;
infile datalines firstobs=2 ;
length Dummy Name $15 Latest_Sales_Date Total_Sales 8;
input dummy $15. Latest_Sales_Date date11. Total_Sales ;
retain name;
name=coalescec(dummy,name);
drop dummy;
datalines;
1234567890123456789012345678901234567890
David Wong 1/Aug/2018 13200
Brian Leung 15/Sep/2018 23450
10/Sep/2018 33000
Mary Chan 3/Jul/2018 45600
20/Aug/2018 37800
1/Sep/2018 21500
30/Aug/2018 42000
John Tam 12/Sep/2018 35000
run;

Re: Creating variables from existing variables

blowsnow__ — Sat, 03 Nov 2018 17:17:37 GMT

But even after running this program, I still have multiple observations for each person. How can my output shows only one name; one Latest_Sales_Date number that is the largest among others; and one Total_Sales sum of all the associated sales amount; for each respective salesperson?

Re: Single observation for each variable

VDD — Sat, 03 Nov 2018 17:37:37 GMT

Why did you start a new post for the same question. refer to your last post and follow through with your issue that is the same topic same question in the same post please.

@blowsnow__ prior post same question

https://communities.sas.com/t5/SAS-Programming/Creating-variables-from-existing-variables/m-p/510186#M137286

Note you also don't have all of the original requirements listed for the same question listed here.

https://communities.sas.com/t5/SAS-Programming/How-to-output-the-last-part-data-programming/m-p/510172

Re: Single observation for each variable

Kurt_Bremser — Sat, 03 Nov 2018 23:00:04 GMT

I merged the two threads.