topic Re: data in SAS Procedures

dataset

librasantosh — Thu, 03 Aug 2017 03:09:03 GMT

How can I create data set with below data

variable names are

data
1176913194483|06/20/1999|||0|1|A1|58|BC|128
1176914599423|10/04/1999|10/15/1999|NEED|1|1|A1|45|AB|72
1176951913656|07/01/2000|||0|1|A1|57|BC|593
1176954000288|05/30/2000|||1|2|A1|47|ON|83

Can anyone help to create data set?

data

librasantosh — Thu, 03 Aug 2017 03:11:00 GMT

Data is

1176913194483 06/20/1999 0 1 A1 58 BC $128.00
1176914599423 10/04/1999 10/15/1999 NEED 1 1 A1 45 AB $72.00
1176951913656 07/01/2000 0 1 A1 57 BC $593.00
1176954000288 05/30/2000 1 2 A1 47 ON $83.00
1176969186303 12/13/2000 1 1 C1 82 BC
1176991056273 08/31/1999 09/18/2000 MOVE 1 1 C1 92 QC $1041.00
1176991866552 05/24/2000 1 1 A1 77 ON

data

librasantosh — Thu, 03 Aug 2017 03:16:00 GMT

how can I show the acctno is unique? what will be the code to see the number of accounts activated and deactivated? Also the earliest and latest activation/deactivation dates available.

Can anyone give me code.

Re: data

Jagadishkatam — Thu, 03 Aug 2017 03:22:47 GMT

Could you please provide sample data and expected output to get better response

Re: data

Jagadishkatam — Thu, 03 Aug 2017 03:25:04 GMT

Could you please provide the variable names with the data and the query with expected output

Re: dataset

Jagadishkatam — Thu, 03 Aug 2017 03:30:13 GMT

Please create a sample txt file as attached and run the below code with the location of sample where you save it

proc import datafile='~path\sample.txt' out=sample dbms=dlm replace;
getnames=yes;
delimiter='|';
run;

Re: data

Shmuel — Thu, 03 Aug 2017 08:00:48 GMT

@librasantosh, please pay attention:

Is this post an answer to your previous post:

https://communities.sas.com/t5/SAS-Procedures/data/m-p/385168

Assuming the answer is YES then:

1) There is no need to open a new thread of posts. Just use REPLY to continue the thread

2) You were asked by @Jagadishkatam to provide variable names, so we have a common language to talk about.

3) I suggest you reply to this post, providing code using the {i} icon or the man in a square icon, like that:

data have;
    input var1 var2 mmddyy10. var3 ...;    /* addapt logic names to variables */
datalines;
1176913194483 06/20/1999 0 1 A1 58 BC $128.00
1176914599423 10/04/1999 10/15/1999 NEED 1 1 A1 45 AB $72.00
1176951913656 07/01/2000 0 1 A1 57 BC $593.00
1176954000288 05/30/2000 1 2 A1 47 ON $83.00
1176969186303 12/13/2000 1 1 C1 82 BC
1176991056273 08/31/1999 09/18/2000 MOVE 1 1 C1 92 QC $1041.00
1176991866552 05/24/2000 1 1 A1 77 ON
; run;

4) What is the issue you have? What kind of help you need ?

Re: data

Kurt_Bremser — Thu, 03 Aug 2017 08:14:56 GMT

@librasantosh wrote:

Data is

1176913194483 06/20/1999 0 1 A1 58 BC $128.00
1176914599423 10/04/1999 10/15/1999 NEED 1 1 A1 45 AB $72.00
1176951913656 07/01/2000 0 1 A1 57 BC $593.00
1176954000288 05/30/2000 1 2 A1 47 ON $83.00
1176969186303 12/13/2000 1 1 C1 82 BC
1176991056273 08/31/1999 09/18/2000 MOVE 1 1 C1 92 QC $1041.00
1176991866552 05/24/2000 1 1 A1 77 ON

And?

Re: dataset

Kurt_Bremser — Thu, 03 Aug 2017 08:48:21 GMT

I merged those seemingly related posts into one.

To @librasantosh:

When you posted your question(s), you saw this right below the posting window:

Stop right there! Before pressing POST, tick off this checklist. Does your post …

✔ Have a descriptive subject line, i.e., How do I ‘XYZ’?

✔ Use simple language and provide context? Definitely mention what version you’re on.

✔ Include code and example data? Consider using the SAS Syntax feature.

You did not

- have a descriptive subject line

- use understandable language that makes it easy for others to understand; keep in mind that most of us use English as a common language without being native English speakers themselves. Also provide context.

- include the code you already tried, and data in a usable form. If you have trouble writing a simple datastep with some datalines, use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 to convert your dataset to a data step and post that here in a code window (7th icon above the posting window)

Re: data

librasantosh — Thu, 03 Aug 2017 09:28:57 GMT

I have tried same method given by you, but data out showing values in different column. 
Forxample, for one row entries are in different then other row entry. 

I hope you have understand my question
data have;
    input var1 var2 mmddyy10. var3 ...;    /* addapt logic names to variables */
datalines;

Re: data

Kurt_Bremser — Thu, 03 Aug 2017 09:36:19 GMT

Please post the WHOLE step including the datalines, and indicate where the resulting dataset did not meet your expectations. If the step failed, post the (once again, whole) log.

Re: dataset

librasantosh — Thu, 03 Aug 2017 09:43:10 GMT

Below is the code I have used for data creation. But table giving values to different place.

Data KRM;
Input acctno actdt mmddyy10. deactdt mmddyy10. deactreason$ goodcredit rateplan dealertype$ AGE Province$ sales;

Datalines;
1176951913656 07/01/2000 0 1 A1 57 BC $593.00
1176954000288 05/30/2000 1 2 A1 47 ON $83.00
1176969186303 12/13/2000 1 1 C1 82 BC
1176991056273 08/31/1999 09/18/2000 MOVE 1 1 C1 92 QC $1041.00
1176991866552 05/24/2000 1 1 A1 77 ON
1176992889500 11/28/2000 1 1 C1 68 AB $72.00
1177000067271 12/23/1999 0 1 B1 75 ON $134.00
1177028996676 09/21/2000 0 1 C1 QC $179.00
1177057406016 09/21/2000 0 1 C1 50 ON $529.00
1177066422248 04/26/1999 01/15/2001 NEED 0 1 A2 55 NS $44.00
1177113886410 09/13/2000 01/08/2001 COMP 0 1 C1 45 ON $63.00
1177128264924 12/10/1999 1 1 B1 38 ON $178.00
;
run;

Re: dataset

Kurt_Bremser — Thu, 03 Aug 2017 10:53:24 GMT

@librasantosh wrote:

Below is the code I have used for data creation. But table giving values to different place.

Data KRM;
Input acctno actdt mmddyy10. deactdt mmddyy10. deactreason$ goodcredit rateplan dealertype$ AGE Province$ sales;

Datalines;
1176951913656 07/01/2000 0 1 A1 57 BC $593.00
1176954000288 05/30/2000 1 2 A1 47 ON $83.00
1176969186303 12/13/2000 1 1 C1 82 BC
1176991056273 08/31/1999 09/18/2000 MOVE 1 1 C1 92 QC $1041.00
1176991866552 05/24/2000 1 1 A1 77 ON
1176992889500 11/28/2000 1 1 C1 68 AB $72.00
1177000067271 12/23/1999 0 1 B1 75 ON $134.00
1177028996676 09/21/2000 0 1 C1 QC $179.00
1177057406016 09/21/2000 0 1 C1 50 ON $529.00
1177066422248 04/26/1999 01/15/2001 NEED 0 1 A2 55 NS $44.00
1177113886410 09/13/2000 01/08/2001 COMP 0 1 C1 45 ON $63.00
1177128264924 12/10/1999 1 1 B1 38 ON $178.00
;
run;

When you read data using delimited (list) input, you have to provide something to be read when values are missing. SAS uses a single dot for this.

Note that you should use the colon right in front of the informat, so SAS does not switch to formatted (fixed-column) input, which requires all character columns to be present (in your case, 10 for the mmddyy10. format).

Finally, the truncover option allows missing values at the end of a dataline, so that SAS does not skip to the next line.

data KRM;
infile cards truncover;
input
  acctno :$13.
  actdt :mmddyy10.
  deactdt :mmddyy10.
  deactreason$
  goodcredit
  rateplan
  dealertype$
  AGE
  Province$
  sales :dollar10.2
;
format
  actdt deactdt mmddyy10.
;
datalines;
1176951913656 07/01/2000 . . 0 1 A1 57 BC $593.00
1176954000288 05/30/2000 . . 1 2 A1 47 ON $83.00
1176969186303 12/13/2000 . . 1 1 C1 82 BC
1176991056273 08/31/1999 09/18/2000 MOVE 1 1 C1 92 QC $1041.00
1176991866552 05/24/2000 . . 1 1 A1 77 ON
1176992889500 11/28/2000 . . 1 1 C1 68 AB $72.00
1177000067271 12/23/1999 . . 0 1 B1 75 ON $134.00
1177028996676 09/21/2000 . . 0 1 C1 . QC $179.00
1177057406016 09/21/2000 . . 0 1 C1 50 ON $529.00
1177066422248 04/26/1999 01/15/2001 NEED 0 1 A2 55 NS $44.00
1177113886410 09/13/2000 01/08/2001 COMP 0 1 C1 45 ON $63.00
1177128264924 12/10/1999 . . 1 1 B1 38 ON $178.00
;
run;

Re: dataset

librasantosh — Thu, 03 Aug 2017 12:07:05 GMT

I agree with these codes, but If I have huge data then How will I put '.' for the all missing values. This has more than thousand observation. Is there any way for this one? Thanks alot for reply

Re: dataset

Kurt_Bremser — Thu, 03 Aug 2017 12:38:08 GMT

There's two possible scenarios:

You are reading your data from an external source. If that is the case, such data must have a reliable structure that allows SAS to detect missing values (two delimiters in succession, for instance)

Or you are preparing a dataset you already have for posting as example here. In that case, you should post just enough observations to illustrate your issue, and you can use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 to automatically create a data step from your dataset that has ALL attributes and contents.

Download the .zip, extract the code, open the code with EG (or an editor and copy/paste it to SAS Studio), and run it once to compile the macro. Then use as documented.

If you prepare example data manually, taking care of missing values is no problem. Just look at the time it took me to correct your code.

Re: dataset

librasantosh — Thu, 03 Aug 2017 12:48:40 GMT

Yes my data is in txt file and its not arranged in order. Is there any solution to create dataset without putting '.' for missing values in raw data. Data values are not arranged in neatly defined columns, but are separated by at least one space and contain some nonstandard characters, such as the dollar sign ($)

Re: dataset

Kurt_Bremser — Thu, 03 Aug 2017 13:26:04 GMT

If an external text file is not structured in a way that lets you detect missing values, return to sender. As soon as you have two numerics in succession, there is no way for you to detect which one shall be set to missing. I know of no software that would export data in such a stupid way, even MS Excel manages to get that right.

Are you sure you made no mistake when copy/pasting to the data step?

Re: dataset

ballardw — Thu, 03 Aug 2017 14:58:23 GMT

@librasantosh wrote:
Yes my data is in txt file and its not arranged in order. Is there any solution to create dataset without putting '.' for missing values in raw data. Data values are not arranged in neatly defined columns, but are separated by at least one space and contain some nonstandard characters, such as the dollar sign ($)

You need to be aware that this forum when pasting into the main message windows will often reformat text with removing blanks one of the most common elements. So post code and data into a code box using the {i} menu icon at the top of the message box to prevent that.

Re: dataset

librasantosh — Fri, 04 Aug 2017 00:54:12 GMT

Hi, thanks for reply,

I am trying this way

proc import datafile="C:\file path.txt"

dbms=dlm

out=new data

replace;

delimiter='|';

getnames=yes;

guessinrows=100;

run;

Its working.

Re: dataset

Kurt_Bremser — Fri, 04 Aug 2017 06:07:06 GMT

@librasantosh wrote:

Hi, thanks for reply,

I am trying this way

proc import datafile="C:\file path.txt"

dbms=dlm

out=new data

replace;

delimiter='|';

getnames=yes;

guessinrows=100;

run;

Its working.

Nice.

What you have now (probably without knowing it) is a working data step. proc import creates it, runs it, and writes it to the log. You can copy it from there and adapt it to your needs for fine-tuning.